July 13, 2005 at 11:41 pm
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
July 15, 2005 at 10:24 am
Not sure what you mean, but you have a couple of options. If you want your rows to be ordered in the same way they're listed in the IN clause, there's no direct way to accomplish that. However, if your Event table has the EventOrder column, you can order by that and insert that into your destination table.
That doesn't look like what you need though. To get the rows insert in a particular order, you will have to order your SELECT clause, or insert the rows one by one, using an increment variable. You might try the identity function:
Select 1 as Col1, EventID, NULL AS Col3, NULL AS Col4, IDENTITY(int, 1, 1) AS EventOrder into #tmp from Event where....
Insert into CompanySeasonProductTemplate
select * from #tmp
...or something like that. Apparently the IDENTITY function only works when using SELECT INTO. You'd still need to order your rows separately for it to work though...
Dylan
Dylan Peters
SQL Server DBA
July 15, 2005 at 11:39 am
Why do you need that "ordering" ?
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply