SQL syntax....

[dodgyone]

I need to be able to copy all of the records from one table into an output table (using ADO by the way). In the process of the copy is a merge between the original table and second table which in turn produces the output table.

The second table will need new columns produced for the data being entered. The problem is that the original table has many many fields to copy and typing these all by hand, adding the second table and then sending to the output table seems very long winded... ie...

INSERT INTO OutputTable(original1,original,original2,original3,original4 etc etc
secondtable1, secondtable2) VALUES (etc etc)

So in conclusion is it possible in the output table to already include these additional columns from table 2 into the output table which has it's structure built to mirror the original table? Then, copy the entire record from the original table in one go (by using * in SQL) and then add the additional data from the second table into that record?

I hope this makes some sense ;o) Thanks

 

[Niv3k]

Try this:

INSERT INTO OutputTable (Field1, Field2, Field3)
SELECT t1.Field1, t1.Field2, t2.Field3 FROM t1 inner join t2 on t1.primarykey=t2.foreignkey

Should be something like that, good luck!

Kevin

 

[dodgyone]

I don't think I explained myself very well....

The first table has over 100 fields and I need all of these to be copied into the output table.

There is a second table which also sends two fields of information to the new table, to the same record as the first record sends it data to.

(A record is being updated here by taking data from table one, searching table two for that value and then updating the output table with the resulting data... the coding to do this is all in hand)

The output table has been created with the first table structure... + ...the two fields required from table two. By using your method then I'm still typing in loads and loads of entries into the SQL statement. I know there's going to be errors and it's going to take ages to finish.

Isn't there a copy all feature to copy all records from table one to the output table and then update the two fields in the output tabel with the second table data? Will there be a problem with the copy all feature if there are two fields in the output table which don't exist in table one?

 

[dodgyone]

Has anybody got any more ideas please... I'm stuck on the project I'm doing until I solve this )

Many thanks....

 

[ricrecar]

You can use SELECT INTO SQL to do this. It worked for me against Access and SQL Server 2000.

This will fail if the object exists, however.


cnnADO.Execute "SELECT *, ' ' as Col1,' ' as Col2 INTO tblNew FROM tblOld"

What this does is copy all records from tblOld to tblNew and creates 2 dummy columns to hold the values from your second table. You can put some default values in for the 2 cols if you want. Note that the dummy cols are padded with one space.

John Curran

 

[daimaou]

ok, I think I finally understood what you want to do

The only thing I'm not getting is this:

Is there any relation between Table1 and Table2? I mean is there any field common to both of them?
If not how can you relation the records of both tables?

Let me know this so I can help you out.

 

[dodgyone]

Thanks for getting back to me...

Table one and table two are not related... what my code does is to take a value from table one and searches in table two for a matching value in a record. If it finds a match then it takes the updated value from that record and sends this along with the entire record from table one into the output table. This continues until all records have been checked against table two.

I hope this has helped a little...

Cheers...

 

[daimaou]

ok. I don't have much time right now so I'll quickly make a scratch of what you should do, then if you still having problems let me know.
I suppose you know how to use ADO right? So I'll just put here the SQL statements you should use for opening records or executing SQL.

First Select what you want from table 1 and put it on FinalTable(use connection.Execute for this):

INSERT INTO FinalTable SELECT * FROM TABLE1 WHERE ....

(Perhaps you want to delete it's records first I don't know...)

Then just Update the fields you want from Table2(use connection.Execute for this):

UPDATE FinalTable SET Field1 = (SELECT FIELD1 FROM TABLE2 WHERE TABLE2.IDField=FinalTable.IDField), Field2 = (SELECT FIELD2 FROM TABLE2 WHERE TABLE2.IDField=FinalTable.IDField)

NOte: Field1 and Field2 are the fields from Table2 that you want to insert into FinalTable.

This should be everything you need. I suppose the IDField is unique for table2 right? I mean if there is a relationship between Table1-Table2 it'll be 1:1.
And other thing... if there is a non matching record between Table1-Table2 the record will still be added to FinalTable, but without filling Field1 and Field 2 right?

Try this, and let me know if you're having problems and I'll get back to you.

 

[dodgyone]

I'm still unsure about how to update the output table... could somebody please simplify the code so that I can understand what's going on or maybe alter my code...

Many thanks....