Inserting Returned Records

hugh@kmcnetwork.com · #1 (**permalink**) 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 · #2 (**permalink**) 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 · #3 (**permalink**) 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
no records returned when trying to insert data	ronny	Classic ASP Databases	1	February 2nd, 2006 12:22 PM
Records not returned with where clause	Trojan_uk	SQL Server 2000	2	December 4th, 2003 10:03 AM
Limited 1000 records returned from Active Directoy	LPetras	.NET Web Services	0	July 30th, 2003 09:45 AM