vba 将一列从一个表复制到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16469003/
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
Copy a column from one table into another table
提问by gta0004
I have tried this with ALTER TABLEto create the column followed by INSERT INTO. This kind of works, except each subsequent column starts after the previous column has ended. I guess this is how insert into works, so is there a workaround or another query I can build?
我试过用这个ALTER TABLE来创建列，然后是INSERT INTO. 这种作品，除了每一列在前一列结束后开始。我想这就是 insert into 的工作原理，那么我可以构建解决方法或其他查询吗？
I have been trying with updates but its not working out. For reference, these were the alter/insert queries i used.
我一直在尝试更新，但没有成功。作为参考，这些是我使用的更改/插入查询。
SQL = "ALTER TABLE [results] ADD COLUMN [" & fld.Name & "_result] TEXT(25)"
db.Execute SQL
SQL = "INSERT INTO [results] ([" & fld.Name & "_result]) SELECT [Result] As
[" & fld.Name & "_result] FROM [newtable]"
db.Execute SQL
回答by criticalfix
Your insert statement assumes that the results table has only one column that you need to insert data into. This is unlikely to be true, if the table already had other columns before you executed the ADD COLUMN.
您的插入语句假定结果表只有一列需要插入数据。如果在您执行 ADD COLUMN 之前表已经有其他列，这不太可能是真的。
You will need to keep track of the columns in the results table, and provide data (or a default value) for each column.
您需要跟踪结果表中的列，并为每一列提供数据（或默认值）。
It is rather unusual to expand a table's structure from inside an application. What are you trying to accomplish? Are you sure you can't accomplish it better by defining fixed tables and then adding data from your application?
从应用程序内部扩展表的结构是很不寻常的。你想达到什么目的？您确定不能通过定义固定表然后从应用程序添加数据来更好地完成它吗？
UPDATE
更新
Okay, I think I understand what you're describing. On the first iteration, the ALTER TABLE creates the first column. The INSERT adds a bunch of rows that have data in this first column.
好的，我想我明白你在描述什么。在第一次迭代中，ALTER TABLE 创建第一列。INSERT 添加了一组在第一列中有数据的行。
On the second interation, the ALTER TABLE creates a second column. The INSERT creates a whole bunch of new rows, but only the second column is populated. The first column is all NULL because you didn't provide values for it. And so on and so forth for the third and subsequent iterations.
在第二次交互中，ALTER TABLE 创建第二列。INSERT 创建了一大堆新行，但只填充了第二列。第一列全部为 NULL，因为您没有为其提供值。依此类推第三次及后续迭代。
If your actual intention is to duplicate the source table and its data, then you should create your results table in a single pass. You know the column structure, right? Use a CREATE TABLE statement. Then write a single INSERT statement somewhat like the following:
如果您的实际意图是复制源表及其数据，那么您应该一次性创建结果表。你知道列结构，对吧？使用 CREATE TABLE 语句。然后编写一个类似于以下的 INSERT 语句：
INSERT INTO [results]
([field1_result], [field2_result], [field3_result])
SELECT [Result] As
[field1_result, [field2_result], [field3_result]]
FROM [newtable]
Is this what you have in mind?
这是你的想法吗？
回答by Ravi Thapliyal
Before you enter into the loop create your [results]table as
在进入循环之前，将[results]表创建为
SQL = "CREATE TABLE [results] SELECT [primary_key] FROM [newtable]"
db.Execute SQL
Then at every iteration of the loop execute
然后在循环的每次迭代中执行
SQL = "ALTER TABLE [results] ADD COLUMN [" & fld.Name & "_result] TEXT(25)"
db.Execute SQL
SQL = "UPDATE [results] SET r.[" & fld.Name & "_result] = n.[Result] " &
"FROM [results] r, [newtable] n " &
"WHERE r.[primary_key] = n.[primary_key]"
db.Execute SQL
So, if you had your [newtable]at its first two iterations like
所以，如果你[newtable]在它的前两次迭代中像
[primary_key] [Results] [primary_key] [Results]
1 A 1 D
2 B 2 E
3 C 3 F
Your [results]table (after the above two iterations) would look like
您的[results]表（在上述两次迭代之后）看起来像
[primary_key] [fld1_result] [fld2_result]
1 A D
2 B E
3 C F
