Copying data from one table to another different column names

Question

I'm having an issue copying one table's data to another. I have around 100 or so individual tables that have generally the same field names but not always. I need to be able to copy and map the fields. example: source table is BROWARD and has column names broward_ID, name, dob, address (the list goes on). The temp table I want to copy it to has ID, name, dob, address etc.

I'd like to map the fields like broward_ID = ID, name = name, etc. But many of the other tables are different in column name, so I will have to write a query for each one. Once I figure out the first on, I can do the rest. Also the column in both tables are not in order either..thanks in advance for the TSQL...

Solution

With tables:

BROWARD (broward_ID, name, dob, address) /*source*/
TEMP (ID, name, address,dob) /*target*/

If you want to copy information from BROWARD to TEMP then:

INSERT INTO TEMP SELECT broward_ID,NAME,ADDRESS,DOB FROM BROWARD --check that the order of columns in select represents the order in the target table

If you want only copy values of broward_ID and name then:

INSERT INTO TEMP(ID, name) SELECT broward_ID,NAME FROM BROWARD

Comments

[Hart CO]

What's the question? What have you tried so far?

[Aramillo]

For that you can use insert into select, but you need to create the query, as you said, if you haven't to do any transformation, then you only must consider the order of the columns

[DLeh]

w3schools.com/sql/sql_insert_into_select.asp

[Sal]

I've tried insert into temptable select name, address, dob where name = name and dob = dob etc etc but it erros with column name or number of supplied values does not match table definition

[Tom Blodget]

Be careful you don't have broward_ID = brevard_ID. Broward seems like data instead of metadata, anyway. One solution would be to have a column for the source table name and set a composite key on the combined table (source_table, id).

[Sal]

What would I do if say I wanted the column in one table to correspond to another in a different table that had a different name and order? Like Such as first to firstname, DOB to date of birth etc.. .regardless of the table order.

[Justin C]

Why does it appear you're inserting into the source table, BROWARD?