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.
Author |
Topic |
Harry C
Posting Yak Master
148 Posts |
Posted - 2005-07-14 : 01:25:05
|
SET @EventIDs = 7,3,4,5INSERT 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 ENDFROM Event WHERE EventID IN (7,3,4,5) Kristen |
|
|
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 |
|
|
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. |
|
|
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, SplitItemFROM Event JOIN dbo.MySplitFunction(@strEventList) ON SplitValue = EventIDSELECT * FROM dbo.MySplitFunction('7,3,4,5') would give:SplitItem SplitValue 1 7 2 3 3 4 4 5 Kristen |
|
|
|
|
|
|
|