SQL Server SELECT into existing table

413
votes

I am trying to select some fields from one table and insert them into an existing table from a stored procedure. Here is what I am trying:

SELECT col1, col2
INTO dbo.TableTwo 
FROM dbo.TableOne 
WHERE col3 LIKE @search_key

I think SELECT ... INTO ... is for temporary tables which is why I get an error that dbo.TableTwo already exists.

How can I insert multiple rows from dbo.TableOne into dbo.TableTwo?

6
sqlsql-servertsqlstored-procedures
Since you've already accepted an answer, I wanted to offer just a note: Select Into isn't "for temporary tables", it is for creating a new table based on the structure (and data) of the select portion of the query. For a table X, you can only ever Select Into it a maximum of 1 time*, after that you need to use Insert Into to append any data. *If the table exists already, then zero times. This is of course unless you DROP the table first. – pinkfloydx33
but do note that Select Into doesn't copy index / primary key / foreign key constraints, so it leaves you with an unindexed heap-o-data. It's useful for quick dev work, but not the way to add / move a real production table. – Graham Griffiths
just run this statement 'drop table tabletwo;' and run above query. Select ...into isn't for temporary tables. – Shiwangini

6 Answers

688
votes

SELECT ... INTO ... only works if the table specified in the INTO clause does not exist - otherwise, you have to use:

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key
22
votes

There are two different ways to implement inserting data from one table to another table.

For Existing Table - INSERT INTO SELECT

This method is used when the table is already created in the database earlier and the data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are not required to list them. It is good practice to always list them for readability and scalability purpose.

----Create testable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable

For Non-Existing Table - SELECT INTO

This method is used when the table is not created earlier and needs to be created when data from one table is to be inserted into the newly created table from another table. The new table is created with the same data types as selected columns.

----Create a new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable

Ref 1 2

3
votes

It would work as given below :

insert into Gengl_Del Select Tdate,DocNo,Book,GlCode,OpGlcode,Amt,Narration 
from Gengl where BOOK='" & lblBook.Caption & "' AND DocNO=" & txtVno.Text & ""
2
votes
select *
into existing table database..existingtable
from database..othertables....

If you have used select * into tablename from other tablenames already, next time, to append, you say select * into existing table tablename from other tablenames

2
votes

If the destination table does exist but you don't want to specify column names:

DECLARE @COLUMN_LIST NVARCHAR(MAX);
DECLARE @SQL_INSERT NVARCHAR(MAX);

SET @COLUMN_LIST = (SELECT DISTINCT
    SUBSTRING(
        (
            SELECT ', table1.' + SYSCOL1.name  AS [text()]
            FROM sys.columns SYSCOL1
            WHERE SYSCOL1.object_id = SYSCOL2.object_id and SYSCOL1.is_identity <> 1
            ORDER BY SYSCOL1.object_id
            FOR XML PATH ('')
        ), 2, 1000)
FROM
    sys.columns SYSCOL2
WHERE
    SYSCOL2.object_id = object_id('dbo.TableOne') )

SET @SQL_INSERT =  'INSERT INTO dbo.TableTwo SELECT ' + @COLUMN_LIST + ' FROM dbo.TableOne table1 WHERE col3 LIKE ' + @search_key
EXEC sp_executesql @SQL_INSERT
0
votes

IF you want a identity column in new table created with select into then it can be done as below.

SELECT 
 ID = IDENTITY(INT, 1, 1),
        name
INTO    table2
FROM table1