Insert Statement incrementing value of previous record in the insert
-
Example:
I have 2 tables
Customer
- custid int (index)
- custname varchar(100)
CustomerWearerItems
- custid int (index)
- wearerid int (index)
- item int (index)
- itemsequence (index)
- itemdescription
I have an application that needs to transfer customeritems from one customer to another.
When I copy items, I have to find out the last itemsequence for a given customer. This field is incremented by 1 for each item added. Same scenario for the wearerid field
Let say we have 2 Customers 100 and 200.
Customer 100 has 2 records in CustomerWearerItems:
CustomerWearerItems:
custid 100, wearerid 1, item 1002, itemsequence 1, itemdescription 'item 1002'
custid 100, wearerid 1, item 1003, itemsequence 2, itemdescription 'item 1003'
Customer 200 has 1 record in CustomerWearerItems
CustomerWearerItems:
custid 200, wearerid 1, item 1005, itemsequence 1, itemdescription 'item 1005'
Now my application needs to copy customer 100 records in CustomerWearerItems and insert into CustomerWearerItems. The tricky part is that I need to increment the itemsequence field and figure out the next available wearer number.
Example of how the records should look after the transfer.
custid 200, wearerid 2, item 1002, itemsequence 2, itemdescription 'item 1002'
custid 200, wearerid 2, item 1003, itemsequence 3, itemdescription 'item 1003'
Currently I am using 1 query to get the last itemsequnce for the new customer (which works - in this example it would return 1)
declare @lastitemsequence int
set @lastitemsequence = (Select ISNULL(MAX(ItemSequence),0) From CustomerWearerItems WHERE custid=200
declare @lastwearerid int
set @lastwearerid = (Select ISNULL(MAX(wearerid),0) From CustomerWearerItems WHERE custid=200
then in my insert statement
Insert Into CustomerWearerItems(custid,wearerid,item,lastitemsequence,itemdescritpion)
SELECT custid,@lastwearerid +1,item,@lastitemsequence + 1,itemdescription
From CustomerWearerItems
Where custid=100 and wearerid=1
What ends up happening is my records will fail on the insert because of a primary key error. Instead of the records incrementing the field correctly like
custid 200, wearerid 2, item 1002, itemsequence 2, itemdescription 'item 1002'
custid 200, wearerid 2, item 1003, itemsequence 3, itemdescription 'item 1003'
It doesn't and looks like this (item sequence doesn't increment for additional records in the insert)
custid 200, wearerid 2, item 1002, itemsequence 2, itemdescription 'item 1002'
custid 200, wearerid 2, item 1003, itemsequence 2, itemdescription 'item 1003'
-
Which field(s) is the primary key defined?
Your issue is want sql to use a "Sequencing" value. But your inserting it set based, which does not provide this functionality.
The only column type that will do this is an Identity.
try this
SELECT custid,WearerID + @lastwearerid,item,ItemSequence + @lastitemsequence,itemdescription
From #Test
Where custid=100 and wearerid=1
It might not work in all situations.
It might end up that you need to insert the records one at a time, and not set based, or update the records with the correct sequence one at at time.
OR
Lose the sequencing requirement, and try to handle it another way. Of course you may not be able to do this, and I don't know the reasons you did it this way so just my $.02
-
Check out this KB Article:
http://support.microsoft.com/default.aspx/kb/186133
Interesting ways to sequence a set of rows, I use an Update after the Insert, but you should be able to combine these into the Insert.
Andy
-
I'm also having similar kind of requirement.
did u get solution to this...if yes pls inform me too.
-
Simple ,
Create an identity on such column.
It will autometically increment the value.
-
yeah true, creating an identity column will solve the purpose.
In my case, table is already existing and lot many screen are depending on this table. Changing table defination is not right choice for me.
I cld achieve it by inserting all data to a a table variable with identity column and then inserting this data to main table.
I was looking for much better approach than this.
-
Well, I think what Sam should have done is the following: Define @lastitemsequence, @lastwearerid as above, then
declare @FirstOldItemSequence int
select @FirstOldItemSequence = isnull(min(itemsequence), 0) from CustomerWearerItems WHERE custid=100
and wearerid = 1
Insert Into CustomerWearerItems(custid,wearerid,item,lastitemsequence,itemdescritpion)
SELECT custid,@lastwearerid +1,item, itemsequence - @FirstOldItemSequence + @lastitemsequence + 1,itemdescription
From CustomerWearerItems
Where custid=100 and wearerid=1
If this doesn't help you (and I guess it doesn't
), please post table definition, sample data, and the expected outcome of the insert statement. Preferably in a new thread, to get maximum response...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply