Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

insert twice seems to pick up new id Expand / Collapse
Author
Message
Posted Wednesday, January 08, 2014 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:47 AM
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
Post #1528998
Posted Wednesday, January 08, 2014 12:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 11,927, Visits: 10,967
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)
Post #1529032
Posted Wednesday, January 08, 2014 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:47 AM
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
Post #1529047
Posted Wednesday, January 08, 2014 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 11,927, Visits: 10,967
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)
Post #1529070
Posted Wednesday, January 08, 2014 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:47 AM
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

Post #1529084
Posted Wednesday, January 08, 2014 2:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 12,738, Visits: 31,040
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1529100
Posted Wednesday, January 08, 2014 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:47 AM
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
Post #1529105
Posted Wednesday, January 08, 2014 2:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 1,058, Visits: 5,731
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
Post #1529109
Posted Wednesday, January 08, 2014 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 2014 6:47 AM
Points: 5, Visits: 8
the enterprise manager says version 8?

Nick
Post #1529116
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse