将一个表的所有值插入到另一个表中的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/576441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert all values of a table into another table in SQL
提问by Vinod
I am trying to insert all values of one table into another. But the insert statement accepts values, but i would like it to accept a select * from the initial_Table. Is this possible?
我试图将一个表的所有值插入到另一个表中。但是插入语句接受值，但我希望它接受来自 initial_Table 的 select *。这可能吗？
回答by Matt Hamilton
The insert statement actually has a syntax for doing just that. It's a lot easier if you specify the column names rather than selecting "*" though:
insert 语句实际上有一个语法可以做到这一点。如果您指定列名而不是选择“*”会容易得多：
INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
-- optionally WHERE ...
I'd better clarify this because for some reason this post is getting a few down-votes.
我最好澄清一下，因为出于某种原因，这篇文章得到了一些反对票。
The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. As others have said, the SELECT ... INTO syntax is for when you want to create the new table as part of the command.
INSERT INTO ... SELECT FROM 语法适用于您插入的表（在我上面的示例中为“new_table”）已经存在的情况。正如其他人所说， SELECT ... INTO 语法适用于您想在命令中创建新表的情况。
You didn't specify whether the new table needs to be created as part of the command, so INSERT INTO ... SELECT FROM should be fine if your destination table already exists.
您没有指定是否需要在命令中创建新表，因此如果目标表已经存在，则 INSERT INTO ... SELECT FROM 应该没问题。
回答by FibreCode
Try this:
尝试这个：
INSERT INTO newTable SELECT * FROM initial_Table
回答by sornalingam
You can insert using a Sub-query as follows:
您可以使用子查询插入，如下所示：
INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where column=value
回答by Otávio Décio
回答by Joe Skora
You can use a select intostatement. See more at W3Schools.
您可以使用select into语句。 在 W3Schools 上查看更多信息。
回答by Mahesh
There is an easier way where you don't have to type any code (Ideal for Testing or One-time updates):
有一种更简单的方法，您无需键入任何代码（非常适合测试或一次性更新）：
Step 1
第1步
- Right click on table in the explorer and select "Edit top 100 rows";
- 右键单击资源管理器中的表格并选择“编辑前 100 行”；
Step 2
第2步
- Then you can select the rows that you want (Ctrl + Clickor Ctrl + A), and Right click and Copy (Note: If you want to add a "where" condition, then Right Click on Grid -> Pane -> SQL Now you can edit Query and add WHERE condition, then Right Click again -> Execute SQL, your required rows will be available to select on bottom)
- 然后你可以选择你想要的行（Ctrl + Click或Ctrl + A），然后右键单击并复制（注意：如果你想添加一个“ where”条件，然后右键单击网格 -> 窗格 -> SQL Now您可以编辑查询并添加 WHERE 条件，然后再次右键单击 -> 执行 SQL，您需要的行将可以在底部选择）
Step 3
第 3 步
- Follow Step 1 for the target table.
- 针对目标表执行步骤 1。
Step 4
第四步
- Now go to the end of the grid and the last row will have an asterix (*)in first column (This row is to add new entry). Click on that to select that entire row and then PASTE (Ctrl + V). The cell might have a Red Asterix (indicating that it is not saved)
- 现在转到网格的末尾，最后一行将在第一列中有一个星号(*)（这一行是添加新条目）。单击它以选择整行，然后粘贴（Ctrl + V）。单元格可能有一个红色的星号（表示它没有保存）
Step 5
第 5 步
- Click on any other row to trigger the insert statement (the Red Asterix will disappear)
- 单击任何其他行以触发插入语句（红色星号将消失）
Note - 1: If the columns are not in the correct order as in Target table, you can always follow Step 2, and Select the Columns in the same order as in the Target table
注意 - 1：如果列的顺序与目标表中的顺序不正确，您可以始终按照步骤 2，并以与目标表中相同的顺序选择列
Note - 2- If you have Identity columns then execute SET IDENTITY_INSERT sometableWithIdentity ONand then follow above steps, and in the end execute SET IDENTITY_INSERT sometableWithIdentity OFF
注意 - 2- 如果你有 Identity 列然后执行SET IDENTITY_INSERT sometableWithIdentity ON然后按照上面的步骤，最后执行SET IDENTITY_INSERT sometableWithIdentity OFF
回答by mika
If you are transferring a lot data permanently, i.e not populating a temp table, I would recommend using SQL Server Import/Export Datafor table-to-table mappings.
如果您要永久传输大量数据，即不填充临时表，我建议使用SQL Server 导入/导出数据进行表到表映射。
Import/Export tool is usually better than straight SQL when you have type conversions and possible value truncation in your mapping. Generally, the more complex your mapping, the more productive you are using an ETL tool like Integration Services (SSIS) instead of direct SQL.
当您的映射中有类型转换和可能的值截断时，导入/导出工具通常比直接 SQL 更好。通常，映射越复杂，使用集成服务 (SSIS) 等 ETL 工具而不是直接 SQL 的效率就越高。
Import/Export tool is actually an SSIS wizard, and you can save your work as a dtsx package.
导入/导出工具实际上是一个 SSIS 向导，您可以将您的工作保存为 dtsx 包。
回答by Chris Ballance
I think this statement might do what you want.
我认为这个声明可能会做你想要的。
INSERT INTO newTableName (SELECT column1, column2, column3 FROM oldTable);
回答by JORGE
Dim ofd As New OpenFileDialog
ofd.Filter = "*.mdb|*.MDB"
ofd.FilterIndex = (2)
ofd.FileName = "bd1.mdb"
ofd.Title = "SELECCIONE LA BASE DE DATOS ORIGEN (bd1.mdb)"
ofd.ShowDialog()
Dim conexion1 = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + ofd.FileName
Dim conn As New OdbcConnection()
conn.ConnectionString = conexion1
conn.Open()
'EN ESTE CODIGO SOLO SE AGREGAN LOS DATOS'
Dim ofd2 As New OpenFileDialog
ofd2.Filter = "*.mdb|*.MDB"
ofd2.FilterIndex = (2)
ofd2.FileName = "bd1.mdb"
ofd2.Title = "SELECCIONE LA BASE DE DATOS DESTINO (bd1.mdb)"
ofd2.ShowDialog()
Dim conexion2 = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + ofd2.FileName
Dim conn2 As New OdbcConnection()
conn2.ConnectionString = conexion2
Dim cmd2 As New OdbcCommand
Dim CADENA2 As String
CADENA2 = "INSERT INTO EXISTENCIA IN '" + ofd2.FileName + "' SELECT * FROM EXISTENCIA IN '" + ofd.FileName + "'"
cmd2.CommandText = CADENA2
cmd2.Connection = conn2
conn2.Open()
Dim dA2 As New OdbcDataAdapter
dA2.SelectCommand = cmd2
Dim midataset2 As New DataSet
dA2.Fill(midataset2, "EXISTENCIA")
