SQL Server SELECT 到现有表

SQL Server SELECT into existing table

提问by Daniel

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.TableTwoalready exists.

我认为SELECT ... INTO ...是用于临时表，这就是为什么我收到一个dbo.TableTwo已经存在的错误。

How can I insert multiple rows from dbo.TableOneinto dbo.TableTwo?

如何从dbo.TableOneinto插入多行dbo.TableTwo？

回答by OMG Ponies

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

SELECT ... INTO ...仅当 INTO 子句中指定的表不存在时才有效 - 否则，您必须使用：

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:

这假设 dbo.TABLETWO 中只有两列 - 否则您需要指定列：

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

回答by Somnath Muluk

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

有两种不同的方法可以实现将数据从一张表插入到另一张表。

For Existing Table - INSERT INTO SELECT

对于现有表 - 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.

当之前已经在数据库中创建了该表并且数据要从另一个表插入到该表中时，使用此方法。如果 insert 子句和 select 子句中列出的列相同，则不需要列出它们。出于可读性和可扩展性目的，始终列出它们是一种很好的做法。

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

对于不存在的表 - 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 12

参考1 2

回答by Vinod Pareek

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

回答by Verena_Techie

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

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

如果你已经使用过select * into tablename from other tablenames，下次要追加，你说select * into existing table tablename from other tablenames

回答by slayernoah

If the destination table does existbut 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