SQL 为另一个表中的每个 id 在表中插入行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/443140/
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 row in table for each id in another table
提问by obj63
I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following.
我尝试在这里搜索类似的解决方案，但没有看到，所以我想知道完成以下任务的最佳方法是什么。
I have a table with 17 million + rows all have a unique ID. We have recently created a new table that will be used in conjunction with the previous table where the foreign key of the new table is the unique id of the old table.
我有一个包含 1700 万行的表，所有行都有一个唯一的 ID。我们最近创建了一个新表，它将与前一个表结合使用，其中新表的外键是旧表的唯一 id。
For ex.
Table 1 - id, field1, field2, field3...
table 2 - table1.id, field1 ...
例如。
表 1 - id、field1、field2、field3... 表 2 - table1.id、field1 ...
The problem is since we are migrating this into a live environment, we need to back fill table 2 with a row containing the id from table 1 for each row in table 1. ex, table 1 - 1, test, null table 2 now needs to have: 1, null, ... and so on for each row that is in table1. The main issue is that the ids are not all sequential in table 1 so we will have to read from table 1 and then insert based of the id of found into table 2.
问题是，由于我们要将其迁移到实时环境中，因此我们需要使用包含表 1 中表 1 中每一行的 id 的行回填表 2。例如，表 1 - 1，测试，空表 2 现在需要对于 table1 中的每一行，有：1、null、... 等等。主要问题是表 1 中的 id 并非都是连续的，因此我们必须从表 1 中读取，然后根据找到的 id 插入表 2。
Is there any easier way to go about this? Thanks in advance Joe
有没有更简单的方法来解决这个问题？提前致谢乔
Also to clarify, table 2 will be new data and the only thing that it will contain from table 1 is the id to keep the foreign key relationship
还要澄清一下，表 2 将是新数据，它包含表 1 中的唯一内容是保持外键关系的 id
Also this is sql server 2000
这也是 sql server 2000
采纳答案by Amy B
You need to read this article.
你需要阅读这篇文章。
What are the most common SQL anti-patterns?
The main issue is that the ids are not all sequential in table 1 so we will have to read from table 1 and then insert based of the id of found into table 2
主要问题是表 1 中的 id 并非都是连续的，因此我们必须从表 1 中读取，然后根据找到的 id 插入表 2
Yes, look at my answer in the above article and write a key-walking loop using Item #2.
是的，请看我在上面文章中的回答，并使用第 2 项编写一个键遍历循环。
Make sure when you write the insert statement, you provide a fieldlist - as I say in Item #1.
确保在编写插入语句时提供了一个字段列表 - 正如我在第 1 项中所说的那样。
回答by kristof
If I understand correctly, you want one record in table2 for each record in table1. Also I believe that apart from the reference to table1, table2 should initially contain blank rows.
如果我理解正确，您希望 table1 中的每条记录在 table2 中有一条记录。另外我相信除了对 table1 的引用之外，table2 最初应该包含空白行。
So assuming
所以假设
table1 (ID, field1, field2, ...)
table2 (ID, table1_ID, fieldA, fieldB,...)
-- where table1_ID is a reference to ID of table1
After creating table2 you can simply run this insert statement
创建 table2 后，您可以简单地运行此插入语句
insert into table2(table1_ID)
select ID from table1
回答by Ryan Guill
I am not sure I am exactly following you, but would something like this work for you?
我不确定我是否完全关注你，但这样的事情对你有用吗？
INSERT INTO table2 ( SELECT field1, field2, field3... FROM table1 )
If I am understanding correctly you want a record in table2 for every record in table1. This will do just that. Just match up your fields in the select in the right order, and specify constants for any fields in table2 that you don't have in table1.
如果我理解正确，您希望为 table1 中的每条记录在 table2 中记录一条记录。这将做到这一点。只需以正确的顺序匹配选择中的字段，并为 table2 中 table1 中没有的任何字段指定常量。
HTH. Let me know if I am not understanding and Ill try to help again.
哈。如果我不理解，请告诉我，我会再次尝试提供帮助。
回答by Binary Worrier
With that many rows you may run into issues with transaction log space, and length of time running large insert transactions.
对于这么多行，您可能会遇到事务日志空间和运行大型插入事务的时间长度问题。
If run time is a constraint I'd seriously recommend using Bcp (or what ever tool is applicable depending on the platform)
如果运行时间是一个限制，我会认真推荐使用 Bcp（或根据平台适用的任何工具）
Select out the id's from the original table, use that to build a Bcp file for the extension table, then Bcp it in.
从原始表中选择 id，使用它为扩展表构建 Bcp 文件，然后将其 Bcp 输入。
You many find it more performant to Bcp in files of 10,000 records instead of one humungus file with 17,000,000 rows.
许多人发现在 10,000 条记录的文件中使用 Bcp 比在一个具有 17,000,000 行的 humungus 文件中性能更高。
Also, you can do this in the back ground before go live, and write a t-sql job to pick up and that may have been inserted after you took the snapshop of id's.
此外，您可以在上线之前在后台执行此操作，并编写一个 t-sql 作业来获取，并且在您获取 id 的 snapshop 后可能已插入该作业。
