SQL Server - Insert into #TempTable question - Asked By Burak Gunay on 30-Jan-07 11:56 AM


I am inserting records intoa temptable from 2 different tables as follows

set @SQL = 'Insert into #TempCourse (...)
                     Select ... from table1 '


set @SQL = 'Insert into #TempCourse (...)
                     Select ...  from table2'

When I check the contents of #TempCourse, for some reason I only see the data from the 1st table. But if I comment out the first insert, then I see the data from the 2nd table!

How can I keep on appending to #TempCourse after the first insert?



Using SELECT to INSERT records - K Pravin Kumar Reddy replied to Burak Gunay on 30-Jan-07 12:04 PM


We'll use the SELECT statement in conjunction with the INSERT statement to make this as easy as possible. Normally, you would code an INSERT statement something like this (using the pubs database):

INSERT authors (au_id, au_lname, au_fname, contract)
VALUES ('123-45-6789', 'Gates', 'Bill', 1)

This will insert one row into the authors table. You could write a program to loop through a set of records and insert them one at a time into another table. SQL Server is designed for set processing. It is optimized to handle groups or sets of records. We can actually replace the VALUES clause with a SELECT statement that will return a set of records. Suppose we have a table called CALIFORNIA_AUTHORS and we want to populate it with the ID and names of the authors from California. The statement would look something like this:

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

This will take the 15 records with State='CA' and load them into the table california_authors. You can use any type of SELECT statement here. It just has to return a record set that matches the columns in the INSERT statement. It number of columns and datatypes must match (or be implicitly convertable).

You can also execute a stored procedure that returns a record set using the EXEC command in place of the SELECT statement.

Response - F Cali replied to Burak Gunay on 30-Jan-07 12:30 PM

Since you are using dynamic SQL statements and local temp tables, you have to put everything in a single dynamic SQL statement and execute it once:

set @SQL = 'Insert into #TempCourse (...)
                     Select ... from table1

                      Insert into #TempCourse (...)
                     Select ...  from table2'

SQL Server Helper - Frequently Asked Questions