Inserting Returned Records

hugh@kmcnetwork.com · November 3rd, 2003, 09:55 PM

Hello Everyone and thanks for your help in advance. I have written a query similar to the following:

SELECT *
FROM tblTemp
WHERE ID NOT IN (
SELECT ID FROM tblMain)

However, what I want to do is, once the recordset is returned, insert them into another table (tblNew). However, I am not sure if this can be done in one SQL statement (or even if it should be). Any help would be greatly appreciated. Thanks.

nbryson · November 4th, 2003, 05:33 AM

Hi

To insert the data into tblNew use the following :-

INSERT tblNew
(col1, col2, col3)
SELECT col1, col2, col3
FROM tblTemp
WHERE ID NOT IN (
SELECT ID FROM tblMain)

I would explicitly give the column names you will be inserting as select * selects all in the table and is generaly bad practice you never know what columns will be added etc.

Nickie

owain · November 4th, 2003, 01:05 PM

If you want to return the recordset as well as insert the returned rows into another table then you will have to write a batch or script to accomplish this. In SQL Server you could do it like this:

Code:

INSERT INTO tblNew
(col1, col2, col3)
SELECT col1, col2, col3
FROM tblTemp
WHERE ID NOT IN (
   SELECT ID FROM tblMain)
GO

SELECT * FROM tblNew

I hope that helps

Regards
Owain Williams