Insert.. select..left join

[lucia]

Hi, I am new here. Hope some one can help me out.

I use sql server database and vb6 as front end.
I want to insert data from table1 to table2. These two tables are exactly the same. But I want to left join table1 with table3 with some criteria. Here is my code.

Sql = "Insert table2 select Table1.*, table3.field1 "
sql = sql & "From table1 left join table3 "
sql = sql & "On table1.field = table3.field"
sql = sql & "Where............"

I got an error msg while I was runing the code: column name or number of supplied value doesn't macth table definition.

Can any one help me out?

Thanks in advance.

[Thread1]

welcome to codeguru!


your destination table and source table/query should have the same definition (ie., number of colums, structure). but in your query, it is obvious that they have different definition because of the extra field - table3.field1 - found in the source.. by the way, will you please explain a bit what is the purpose of Table3? are you going to use the column Table3.field1 in replace with a column in Table2?

[lucia]

Thank you very much.

The purpose for the table3 is that in my vb form, I have some text boxs to let user put in the criteria. Then i use querry to select the record(s) to meet users requirement. One of the text box data is from table 3.

Thanks again.