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 |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2002-03-12 : 18:45:31
|
| I have a select statement that gets the results I need for a report. I need in the same stored proc, if possible, to insert a record in a table for each record in my select. Is there a way to do this type of thing, without cursors? Or should I break down and open BOL for cursors? If Cursors are the solution, anyone have a link handy for cursor newbies like me?I think the solution might be something like this...INSERT INTO TableA Select field1 from TableB where field1 = @field1But, my select statement has more than just the fields that go into TableB. I need to return my select statement so that I can render the report. The best solution I came up with was to do this:Big Select Statement hereINSERT INTO TableA Big Select statement with only the fields needed for tableAI run the select statement twice (is this a problem, or will SQL retun the second select nearly instantly?), and it's possible data could change between the execution of the first select vs teh second select, which would be very bad in my case.Sorry this is so long! Thanks for any help / pointers!Michael |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-12 : 18:52:12
|
set nocount onselect * into #a from <big select statement>insert TableA (col1, col2, ...)select c1, c2, ...from #aselect * from #adrop table #aSee below for cursors  .==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-12 : 18:52:18
|
| Why would you need to run the SELECT statement twice? Is there some other operation between the first SELECT and the INSERT operation that relies on the SELECT?You could perform the full SELECT into a temp table, then SELECT only the columns you need from the temp table. Unless the rowcount will be very large (100,000+) this should work fine.DEFINITELY NO CURSORS! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-12 : 18:53:39
|
That makes it a good day  .==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-12 : 19:01:34
|
| Doh! Why didn't I think of that! Thanks guys, you rock! Sub 10 minute solution, gotta love SQL Team!I'll probably use table variables instead of temp tables (sql2k rocks!) but either way will get the job done!Thanks again!Michael |
|
|
|
|
|
|
|
|
Select and Insert