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?
7 Answers
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 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 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 & "" 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 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
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 If you want to insert into Table_A, from Table_B, only if the column is not in Table_A, then use the following:
BEGIN TRANSACTION INSERT INTO dbo.Table_A (Column_1) SELECT DISTINCT Some_Column AS Column_1 FROM dbo.Table_B WHERE Some_Column NOT IN (SELECT DISTINCT GroupId FROM dbo.Table_A) COMMIT 
