SQL Server SELECT enters the existing table

I tried to select some fields from a table and insert them from the stored procedure into the existing table. This is what I'm trying:

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

I think select... Into... Is a temporary table, which is why I get the error that dbo.TableTwo already exists.

How to insert dbo.TableTwo from dbo.TableOne dbo.TableTwo?

#1 building

Its working principle is as follows:

insert into Gengl_Del Select Tdate,DocNo,Book,GlCode,OpGlcode,Amt,Narration 
from Gengl where BOOK='" & lblBook.Caption & "' AND DocNO=" & txtVno.Text & ""

#2 building

select *
into existing table database..existingtable
from database..othertables....

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

#3 building

Select... INTO... Is only valid if the table specified in the INTO clause does not exist - otherwise, you must use:

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

Suppose there are only two columns in dbo.TABLETWO - otherwise you need to specify the columns:

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

#4 building

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

For existing tables - INSERT INTO SELECT

This method is used when you have previously created a table in the database and inserted data from another table into the table. If the columns listed in the insert clause and the select clause are the same, they do not need to be listed. It's a good habit to always list them for readability and scalability purposes.

----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 tables - SELECT INTO

You need to use this method when you have not previously created a table, and you need to create it when data from one table is inserted from another into the newly created table. Creates a new table with the same data type as the selected column.

----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

Reference resources 1 2

#5 building

If the target table does exist but you do not want to specify a column name:

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

Tags: Database Stored Procedure xml

Posted on Tue, 18 Feb 2020 21:59:18 -0800 by michaeru