SQL Server 2005 T-SQL remap columns

Question

I have an application that is ready to go live, once we take data from a MS Access DB and import it into SQL Server 2005. I have used the Migration - Access tool to get the Access db into SQL Server, but now I need to take the data from that table and put it into the tables that our app is going to use. Is there a T-Sql way to Insert multiple rows, while at the same time 're-mapping' the data?

For example

SELECT ID, FIRST_NAME, LAST_NAME
INTO prod_users (user_id, first_name, last_name)
FROM test_users

I know that select * into works when the column names are the same, but the

prod_users (column names, ..., ...)

part is what I really need to get to work.

Any ideas?

Answer 1

INSERT and SELECT are the magic keywords:

insert into new_table (list of columns) select columnA, functionB(), 
(correlated_subquery_C) from table_or_join where critera_expression_is_true

Maybe you can be more specific about what you mean by re-mapping?

Base on your comment, a more specific query is:

insert into new_table (user_id, firstname, lastname) 
select id, first_name, last_name from old_database..old_table

Answer 2

I believe the SELECT INTO syntax is used to create new tables. If you want to map data from the tables you just imported to some other existing tables, try a plain INSERT. For example:

INSERT INTO prod_users (user_id, first_name, last_name)
SELECT ID, FIRST_NAME, LAST_NAME
FROM test_users

The mapping of columns from test_users to prod_users is based on the order that they are listed, i.e. the first column in "prod_users (column_names, ...)" matches the first column in the "SELECT other_col_names, ...", the second matches the second, etc. Therefore, in the code sample above, ID is mapped to user_id, and LAST_NAME is mapped to last_name, and so on.

Just make sure you have the same number of columns in each list and that the column types match (or can be converted to match). Note that you don't have to specify all the columns in either table (as long as the destination table has valid defaults for the unspecified columns).

See the INSERT syntax for details; the part relevant to your question is the "execute_statement" expression.