Copy data from tables in one db to duplicate tables in another db - What's the SQL?

Question

Hi folks

I have a situation where i have 20 Access databases. Each has the same 15 tables, but with different data. (The name and schema is identical for each table across the 20 databases).

I want to make a new database with the same structure, and populate it with the contents of all of the 20 original databases, so i have one database with 15 big tables instead of 20 databases with 15 smaller tables.

I have to do it all in SQL, as i'm interfacing via this Ruby Acess library - http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html

Can anyone help me out with the SQL? All i really need i think is an example of appending all the records from one table in a source db to one table in a destination db.

Oh and to make things a little more complicated, each of the fifteen tables in each db has a different set of fields, and some have a lot of fields (40 or so). So, the perfect lazy solution would not require me to list every field in the table when i copy the rows across. Is this possible?

thanks max

Answer 1

If you have SQL server available you could use SQL Server Integration Services (SSIS).

With that you can use a GUI and wizards to transfer the data.

Answer 2

Basic sql would be a "insert into select" statement. To make it shorter I'll call your 20 databases like DB1, DB2, .. DB20 and your "final" database DBFinal.

The lazy solution would requere getting field names from the master shema - its better to list the fields (asuming u can autogenerate the sql statements or use any from your aplication or ER diagrams)

Logic:

1) create DBFinal, disable autoincrement for primary keys (so u can insert primary key values from DB1, DB2, ... 2) generate a select that selects all rows and columns for each table. like:

SELECT key, atrb1, atrb2 FROM table1

3) by using text-copy paste assemble a "insert into select" statement something like:

INSERT into DBFinal.user.table1 (key, atrb1, atrb2) SELECT key, atrb1, atrb2 FROM table1

4) If u got same key values in different databases (for different values) u need to change all primary - foreign key values so u dont get duplicates in the target database. I recommend adding a value (row count + 1 in DBFinal). INSERT into table1 (key, atrb1, atrb2) IN DBFinal SELECT key+9001, atrb1, atrb2 FROM table1