blocks|key|1099954|text|仅当INTO子句中指定的表不存在时，SELECT+...+INTO+...才有效-否则，您必须使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1099955|INSERT+INTO+dbo.TABLETWO
SELECT+col1,+col2
++FROM+dbo.TABLEONE
+WHERE+col3+LIKE+@search_key|code-block|syntax|javascript|1099956|这假设dbo.TABLETWO中只有两列-否则您需要指定这两列：|1099957|INSERT+INTO+dbo.TABLETWO
++(col1,+col2)
SELECT+col1,+col2
++FROM+dbo.TABLEONE
+WHERE+col3+LIKE+@search_key|1099958|entityMap^0|I|J|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

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

<pre><code>INSERT INTO dbo.TABLETWO
SELECT col1, col2
 FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key
</code></pre>

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

<pre><code>INSERT INTO dbo.TABLETWO
 (col1, col2)
SELECT col1, col2
 FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key
</code></pre>

blocks|key|885221|text|它的工作原理如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|885222|insert+into+Gengl_Del+Select+Tdate,DocNo,Book,GlCode,OpGlcode,Amt,Narration+
from+Gengl+where+BOOK='"+&+lblBook.Caption+&+"'+AND+DocNO="+&+txtVno.Text+&+""|code-block|syntax|javascript|885223|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It would work as given below :

<pre><code>insert into Gengl_Del Select Tdate,DocNo,Book,GlCode,OpGlcode,Amt,Narration 
from Gengl where BOOK='" &amp; lblBook.Caption &amp; "' AND DocNO=" &amp; txtVno.Text &amp; ""
</code></pre>

blocks|key|885284|text|select+*
into+existing+table+database..existingtable
from+database..othertables....|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|885285|如果您已经在下一次使用过select+*+into+tablename+from+other+tablenames进行追加，您可以说select+*+into+existing+table+tablename+from+other+tablenames|unstyled|offset|length|style|CODE|885286|entityMap^0|0|C|19|1U|1O|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]|$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|S|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>select *
into existing table database..existingtable
from database..othertables....
</code></pre>

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

blocks|key|1100119|text|有两种不同的方法可以实现将数据从一个表插入到另一个表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1100120|For+Existing+Table+-+INSERT+INSERT+INSERT|1100121|当先前已经在数据库中创建了表，并且要将数据从另一个表插入到此表中时，使用此方法。如果insert子句和select子句中列出的列相同，则不需要列出它们。出于可读性和可伸缩性的目的，最好总是列出它们。|1100122|----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|code-block|syntax|javascript|1100123|对于不存在的表-选择INTO|1100124|此方法用于未早先创建表的情况，并且需要在将一个表中的数据从另一个表插入到新创建的表中时创建。创建的新表的数据类型与选定列的数据类型相同。|1100125|----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|1100126|Ref+1+2|offset|length|1100127|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/2688281/select-into-table-will-it-work-for-inserting-data-into-existing-table?noredirect=1&lq=1|1|https://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table/^0|0|0|0|0|0|0|0|4|1|0|6|1|1|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|H|7|1A|8|@]|9|@]|A|$I|J]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$S|1C|T|1D|1|1E]|$S|1F|T|1G|1|1H]]|A|$]]|$1|U|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]|12|$5|X|Y|Z|A|$10|13]]]]

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

<h2>For Existing Table - INSERT INTO SELECT</h2>

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.

<pre><code>----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
</code></pre>

<h2>For Non-Existing Table - SELECT INTO</h2>

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.

<pre><code>----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
</code></pre>

Ref <a href="https://stackoverflow.com/questions/2688281/select-into-table-will-it-work-for-inserting-data-into-existing-table?noredirect=1&amp;lq=1">1</a> <a href="https://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table/" rel="noreferrer">2</a>

blocks|key|885380|text|如果您希望在使用select+into创建的新表中包含一个identity列，则可以执行以下操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|885381|SELECT+
+ID+=+IDENTITY(INT,+1,+1),
++++++++name
INTO++++table2
FROM+table1++++|code-block|syntax|javascript|885382|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

IF you want a identity column in new table created with select into then it can be done as below.
<pre><code>SELECT 
 ID = IDENTITY(INT, 1, 1),
 name
INTO table2
FROM table1 
</code></pre>

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:

<pre><code>SELECT col1, col2
INTO dbo.TableTwo 
FROM dbo.TableOne 
WHERE col3 LIKE @search_key
</code></pre>

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

How can I insert multiple rows from <code>dbo.TableOne</code> into <code>dbo.TableTwo</code>?

SQL Server SELECT into existing table

数据库

我正在尝试从一个表中选择一些字段，并将它们插入到一个存储过程中的现有表中。这是我正在尝试的：SELECT col1, col2INTO dbo.TableTwo FROM dbo.TableOne WHERE col3 LIKE @search_key我认为SELECT ... INTO ...是用于临时表的，这就是为...

问SQL Server SELECT到现有表中
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server SELECT到现有表中EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server SELECT到现有表中
EN