将一个表的所有值插入到SQL中的另一个表中

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?

我试图将一个表的所有值插入到另一个表中。但是insert语句接受值，但是我希望它接受initial_Table中的select *。这是可能的吗?

10 个解决方案

#1

240

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.

插入……SELECT FROM语法用于当您要插入的表(在我上面的示例中为“new_table”)已经存在时。正如其他人所说，选择……当您想要将新表作为命令的一部分创建时，可以使用语法。

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.

您没有指定是否需要将新表作为命令的一部分创建，因此插入到…如果目标表已经存在，那么SELECT FROM应该没问题。

#2

Try this:

试试这个:

INSERT INTO newTable SELECT * FROM initial_Table

#3

You can insert using a Sub-query as follows:

您可以使用子查询插入如下:

INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where column=value

#4

From here:

从这里开始:

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

#5

You can use a select into statement. See more at W3Schools.

您可以使用select into语句。多见于W3Schools。

#6

If you are transferring a lot data permanently, i.e not populating a temp table, I would recommend using SQL Server Import/Export Data for table-to-table mappings.

如果你要永久传输大量数据，i。如果不填充临时表，我建议使用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更好。通常，映射越复杂，使用集成服务(Integration Services, SSIS)之类的ETL工具而不是直接使用SQL的效率就越高。

Import/Export tool is actually an SSIS wizard, and you can save your work as a dtsx package.

导入/导出工具实际上是一个SSIS向导，您可以将工作保存为dtsx包。

#7

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 + Click or 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 +单击或Ctrl + A),点击右键,复制(注意:如果你想添加一个“地方”状态,然后右键单击SQL网格- >面板- >现在你可以编辑查询和添加条件,然后再次点击右键- >执行SQL,您需要行可以选择在底部)

Step 3

步骤3

Follow Step 1 for the target table.
对目标表执行步骤1。

Step 4

步骤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)
现在转到网格的末尾，最后一行将在第一列中有一个asterix(*)(这一行将添加新条目)。单击它，选择整个行，然后粘贴(Ctrl + V)。

Step 5

步骤5

Click on any other row to trigger the insert statement (the Red Asterix will disappear)
单击任何其他行以触发insert语句(红色的Asterix将消失)

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 ON and then follow above steps, and in the end execute SET IDENTITY_INSERT sometableWithIdentity OFF

注意- 2——如果您有标识列，那么执行SET IDENTITY_INSERT sometableWithIdentity，然后执行上面的步骤，最后执行SET IDENTITY_INSERT sometableWithIdentity

#8

I think this statement might do what you want.

我认为这句话可以做你想做的事。

INSERT INTO newTableName (SELECT column1, column2, column3 FROM oldTable);

#9

insert into stock_take_item_copy (stock_take_id, item_id) select stock_take_id, item_id from mymissingdata

插入到stock_take_item_copy (stock_take_id, item_id)中，从mymissingdata中选择stock_take_id, item_id

Why I got #1062 - Duplicate entry '20239' for key 'PRIMARY'? how to alter the primary key in which table?

为什么我得到#1062 -重复入口'20239'为关键'主'?如何更改表中的主键?

#10

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

#1

240