insert twice seems to pick up new id

Question

insert twice seems to pick up new id

nick 91670

Valued Member

Points: 53
More actions
January 8, 2014 at 10:49 am

#98870

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nick 91670 Valued Member Points: 53 More actions · Answer 2

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 SSC Guru Points: 286590 More actions · Answer 3

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nick 91670 Valued Member Points: 53 More actions · Answer 4

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

End

Lowell SSC Guru Points: 323518 More actions · Answer 5

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 Valued Member Points: 53 More actions · Answer 6

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 SSC-Insane Points: 24260 More actions · Answer 7

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

SELECT * FROM #tbl_directdebits

Are you really using SQL Server 2000?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

nick 91670 Valued Member Points: 53 More actions · Answer 8

nick 91670

Valued Member

Points: 53

January 8, 2014 at 2:54 pm

#1678605

the enterprise manager says version 8?

Nick