insert twice seems to pick up new id

  • 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

  • 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/

  • 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

  • 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/

  • 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

  • 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!

  • 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

  • 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?


    [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]

  • the enterprise manager says version 8?

    Nick

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

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