Anyone ever see/use thisINSERT/UNION ALL method?

Still a bit of an SQL noob, so it may not be anything new here. The nice thing is that you can use this in a single Expression Item (or Action Item, if you're legacy).

Also note that SQL Server 2008 is suuposed to make more efficient use Insert transactions and shiny new structures, but for 2000 (which is what I got saddled with), this makes life a bit easier.

One thing to look out for using that INSERT SELECT method is that you cannot select information from the same table you are inserting into.

Ok, I may very well regret asking this, but why would you want to? I only say that because it seems cicurlar. Kind of like using an Excel formula that points to itself.

Actually I was thinking in terms of how a transaction group works. Normally it would store things in a wide format. Maiking the items read only and referencing them in an INSERT SELECT method gives you option of storing in a tall format.

I can provide an example of why -

I have a rather wide table that has 3 separate insert queries associated with it(Fix32 had a limit to the number of items per SQD block). My coworker was trying to use this basically to combine each 'set' of 3 rows into 1 coherent row.

Thanks for replying, Dravik.


Meaning that he was taking three rows out of a table and inserting it as one row in the same table? I'm really not trying to be dense, I'm just trying to visualize this.

Sure, he had a table that looked like this


*,*,*,1,2,3,*,*,*,2010-01-01 00:00:00
4,5,6,*,*,*,*,*,*,2010-01-01 00:00:00
*,*,*,*,*,*,7,8,9,2010-01-01 00:00:00

and he wanted to combine those rows into one like this

4,5,6,1,2,3,7,8,9,2010-01-01 00:00:00


We're doing it w/ a temp table right now, but you can see where the idea of doing an insert select from the same table would have been cool

I did something like this recently. It's a pain to do it in SQL, so I added a RawData property to the table with the SQL query to get the raw set. ie:And I added a propertyChange script that massaged the raw data into the new set and put it in the 'data' property of the table.

This is not elegant, but it works. One thing you have to look out for is that allowing the user to update this new row will be more work than usual.