Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


insert twice seems to pick up new id


insert twice seems to pick up new id

Author
Message
nick 91670
nick 91670
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
I have a SP that is creating two new records that are copies of the original (so I get three near identical records)

the following two lines (simplified) are run one after the other, the @id is the is the id of the original record (e.g. 123).

the lines SHOULD insert a new record with the field 'duplicateof' equal to the original id

insert into tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from tbl_directdebits where id= @id

insert into tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from tbl_directdebits where id= @id

But when I run this, the first record has the correct id as duplicateof (e.g. 123), but the second has the duplicateof id of the record that was produced by the line before (e.g.124).

Does an insert automatically update @id to the inserted record id? That is the only thing I can think of.

If so would just changing the variable name solve the problem (e.g. from @id to @dupid) so I use insert into tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @dupid from tbl_directdebits where id= @dupid or would that just get updated as well?

Nick
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
nick 91670 (1/8/2014)
I have a SP that is creating two new records that are copies of the original (so I get three near identical records)

the following two lines (simplified) are run one after the other, the @id is the is the id of the original record (e.g. 123).

the lines SHOULD insert a new record with the field 'duplicateof' equal to the original id

insert into tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from tbl_directdebits where id= @id

insert into tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from tbl_directdebits where id= @id

But when I run this, the first record has the correct id as duplicateof (e.g. 123), but the second has the duplicateof id of the record that was produced by the line before (e.g.124).

Does an insert automatically update @id to the inserted record id? That is the only thing I can think of.

If so would just changing the variable name solve the problem (e.g. from @id to @dupid) so I use insert into tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @dupid from tbl_directdebits where id= @dupid or would that just get updated as well?

Nick


Can you post the actual code you are running? An insert statement will not change the value of a variable. I suspect you are in a loop or something like that which is incrementing that value.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nick 91670
nick 91670
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
There are no loops, I make an update to the original record, then copy it to another table, I then make another update to the original record, then the two lines create copies of the original.

I know from the second update that the id is correct before the two lines are run and when the first line is run, the id is correct, but the second line seems to be reading the new record?

Nick
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
nick 91670 (1/8/2014)
There are no loops, I make an update to the original record, then copy it to another table, I then make another update to the original record, then the two lines create copies of the original.

I know from the second update that the id is correct before the two lines are run and when the first line is run, the id is correct, but the second line seems to be reading the new record?

Nick


I can guarantee that your insert statement is absolutely not reading from a table and changing the value of a variable. The only way the value of a variable can change is by some code that changes it. As I asked previously, can you post the actual code you are running?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nick 91670
nick 91670
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Nick Ashton
-- Create date: 15/04/2013
-- Description: updates tbl_directdebits ready for next months payment
--
-- =============================================
ALTER PROCEDURE [dbo].[failedDD]
-- Add the parameters for the stored procedure here
@processdate datetime, /-- date of pocess
@id int, /-- id of current record
@duplicate int /--add one or two duplicates
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Declare @period varchar(50)

--remove any previous duplicates
delete from tbl_directdebits where duplicateof=@id

--update unpaid
update tbl_directdebits set
dnotes='Unpaid ' +CAST(nextdue AS VARCHAR) + ',duplicate made' + dnotes
where id =@id and nextdue=@processdate

-- copy payment record to paid directdebits table (paid amount=0)
insert into tbl_paid_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, notes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel) select patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, 0, servicelevel from tbl_directdebits where id =@id

--update current record for next month
update tbl_directdebits set
nextdue=dateadd(month, 1, convert(datetime, nextdue, 101))
where id =@id

--make duplicates
if(@duplicate=1)
Begin
insert into tbl_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, duplicateof) select patientid, ref, recurperiod, 1, paytodent, smilecarefee, smilecarevat, 0, 0, 0, 0, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, 'Duplicate payment', transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, @id from tbl_directdebits where id= @id
End

if(@duplicate=2)
Begin
insert into tbl_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, duplicateof) select patientid, ref, recurperiod, 1, paytodent, smilecarefee, smilecarevat, 0, 0, 0, 0, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, 'Duplicate payment', transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, @id from tbl_directdebits where id= @id
insert into tbl_directdebits (patientid, ref, recurperiod, recurtimes, paytodent, smilecarefee, smilecarevat, registrationpat, registrationdent, registrationpatvat, registrationdentvat, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, dnotes, transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, duplicateof) select patientid, ref, recurperiod, 1, paytodent, smilecarefee, smilecarevat, 0, 0, 0, 0, patientfee, patientfeevat, insurancefee, insurancefeevat, advancenotice, nextdue, lastedited, startdate, accountid, 'Duplicate payment', transcode, status, treatmentlevel, gdctopay, oneoff, lastpaid, amount, servicelevel, @id from tbl_directdebits where id= @id
End
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 38992
well, if a given id exists more than once in your tbl_directdebits, it will insert multiple rows.

...from tbl_directdebits where id= @id



does this query return any rows?

SELECT id, count(id)
FROM tbl_directdebits
GROUP BY id
HAVING count(id) > 1



based on that command delete from tbl_directdebits where duplicateof=@id id say that whatever logic you have in palce that updates/populates the column duplicateof is broken or failing, if that is how you test for whether an id exists or not.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nick 91670
nick 91670
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
The first comment - I am inserting (a copy) in to the database, using insert into tbl_directdebits (....) select .... from tbl_directdebits where id=@id (the original record)

Second comment - there are not multiple rows with the same id, the table has an auto id field (id) and a duplicateof field.

I greatly appreciate you looking at his for me, but I have been fiddling with it and have added a new variable @dupid, then set @dupid=@id before the two lines, I then used @dupid in the insert
......, servicelevel, @dupid from tbl_directdebits where id= @id

That seems to have cured the problem. Sorry if I have wasted your time, although I still do not understand what was wrong.

Nick
ChrisM@home
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 9736
Looking at the original code, it appears fine. A test harness works fine too, showing that your theory is sound:
CREATE TABLE #tbl_directdebits (
ID INT IDENTITY(1,1),
ref VARCHAR(2),
recurperiod VARCHAR(2),
servicelevel VARCHAR(2),
duplicateof INT)

INSERT INTO #tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) VALUES
('A', 'R', 'S', NULL),
('B', 'R', 'S', NULL),
('C', 'R', 'S', NULL),
('D', 'R', 'S', NULL)

SELECT * FROM #tbl_directdebits

DECLARE @id INT
SET @ID = 3

insert into #tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from #tbl_directdebits where id= @id

insert into #tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from #tbl_directdebits where id= @id

SELECT * FROM #tbl_directdebits



Are you really using SQL Server 2000?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
nick 91670
nick 91670
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
the enterprise manager says version 8?

Nick
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search