sql - Copy a column from one table into another table -

i have tried alter table create column followed insert into. kind of works, except each subsequent column starts after previous column has ended. guess how insert works, there workaround or query can build?

i have been trying updates not working out. reference, these alter/insert queries used.

sql = "alter table [results] add column [" & fld.name & "_result] text(25)" db.execute sql sql = "insert [results] ([" & fld.name & "_result]) select [result]      [" & fld.name & "_result] [newtable]" db.execute sql 

your insert statement assumes results table has 1 column need insert data into. unlikely true, if table had other columns before executed add column.

you need keep track of columns in results table, , provide data (or default value) each column.

it rather unusual expand table's structure inside application. trying accomplish? sure can't accomplish better defining fixed tables , adding data application?

update

okay, think understand you're describing. on first iteration, alter table creates first column. insert adds bunch of rows have data in first column.

on second interation, alter table creates second column. insert creates whole bunch of new rows, second column populated. first column null because didn't provide values it. , on , forth third , subsequent iterations.

if actual intention duplicate source table , data, should create results table in single pass. know column structure, right? use create table statement. write single insert statement following:

insert [results]  ([field1_result], [field2_result], [field3_result])  select [result]  [field1_result, [field2_result], [field3_result]]  [newtable] 

is have in mind?