Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Incrementing a value while using INSERT INTO

Author  Topic 

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-14 : 01:25:05
SET @EventIDs = 7,3,4,5
INSERT INTO CompanySeasonProductTemplate
SELECT 1 ,EventID , NULL , NULL, NULL FROM Event WHERE EventID IN (7,3,4,5)

This is generally how my dynamic SQL works. And it works fine except for one thing. When they are inserted, they are reordered like 3,4,5,7. Is there some way I can get them to stay ordered the way they were? OR, can I somehow set the value of the bolded NULL above(Which is an EventOrder) to the appropriate number? (7=EvenOrder 1, 3 = EventOrder 2 and so on...) Thanks alot

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 01:27:54
Something like this?

INSERT INTO CompanySeasonProductTemplate
SELECT 1 ,EventID , NULL , NULL,
CASE EventID
WHEN 7 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
ELSE 0
END
FROM Event WHERE EventID IN (7,3,4,5)

Kristen
Go to Top of Page

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-14 : 08:28:42
Yes, except for the fact that I will never know what that value is, hence I could not use that method...I wont ever know I am getting back 4 values, or 3 or 1 or 20...so I don't think I could use a CASE...I will keep trying. Thanks alot for taking a look
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-14 : 10:17:59
You can insert them one at time into table variable with identity column as a pk. Then insert ... select ... join @tableVariable tv ... order by tv.tvID.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 11:45:55
Your SELECT is not going to have any known order - unless it has an ORDER BY.

If that's the case, or you can actually apply an ORDER BY, then use an IDENTITY column as mmarovic says.

If the "order" is externally defined (as you seemed to indicate in your code snippet: SET @EventIDs = 7,3,4,5) then you could "split" the comma separately list into a temporary table [again, with Identity numbers] and then JOIN that to the SELECT making the INSERT and get the Identity number from that.

So ... the CASE statement in my example would be replaced by an Identity value from a temporary table.

Assuming you make a "CSV split" function MySplitFunction that returns columns SplitItem (the Identity, i.e. the row/Item number) and SplitValue then the code might look something like this:

DECLARE @strEventList varchar(8000)
SET @strEventList = '7,3,4,5'

INSERT INTO CompanySeasonProductTemplate
SELECT 1 ,EventID , NULL , NULL, SplitItem
FROM Event
JOIN dbo.MySplitFunction(@strEventList)
ON SplitValue = EventID


SELECT * FROM dbo.MySplitFunction('7,3,4,5')
would give:
SplitItem SplitValue
1 7
2 3
3 4
4 5

Kristen
Go to Top of Page
   

- Advertisement -