SQL 2008R2: Looking for a fast way to do a 'INSERT INTO target <all columns except two> SELECT <all columns> FROM source'

I'm doing a SQL-to-SQL conversion, and have 50+ tables to convert from old (source) to new (target) database. I think the answer is 'there's no really fast way to do this', but I'll ask the question anyways.

Each 'group' has..
Two Source tables: Anywhere from 10 to 700 columns.
These two tables have the same schema, although some columns have different collations.
Target tables: Number of columns = Columns in source tables + 2, as I added start_dt and end_dt.

I can't do a 'INSERT INTO Target SELECT * FROM Source' because of the two extra columns.

Question: What's the fastest way to do a 'INSERT INTO target <all columns except two> SELECT <all columns> FROM source'?

Using a view in the designer I don't see a way to select all and have it show all columns, and then just remove the two I don't need. * displays as * instead of all column names.

If the table names match, something like below. If not, we can join on relative column #, with offsets for the 2 extra columns. May need some tweaking but you should be able to gen the code pretty quickly

then add those 2 columns at the end (or you can insert those columns in 3rd&4th position)
or add them to the end and then use design table to move those columns to the proper location (why do you need those ones in 3rd & 4th position, and why not the end?)

If the table names match, something like below. If not, we can join on relative column #, with offsets for the 2 extra columns. May need some tweaking but you should be able to gen the code pretty quickly for all tables, then run it as needed:

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.