T-SQL to insert data from on SQL Server DB to another

I need some help in writing T-SQL to take data from on SQL Server 2005 DB and insert it into tables in another SQL Server 2005 DB.  I've got one catch, the column names don't always match.  For example:
  "Data from" column name: Issued BOM ID
  "Data to" column name: Issued_BOM_ID
The data types are the same but the "Data to" DB may have additional columns that are not in the "Data from" DB.  I would expect these to be left a the default or null.

Also, the table names don't always match as follows:
  "Data from" table name: Issued BOM
  "Data to" table name: Issued_BOM
I think this part could be handled by having a list of "Data from" table names and "Data to" tables names and pass them to the procedure.

Anyone have any suggestions on how to write this T-SQL?

schmir1Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:

Use DestinationDB 
INSERT INTO dbo.DestinationTable  --- say this has 5 columns 
SELECT Col1, null, null, null, ''
FROM SourceDB.dbo.SourceTable <Where Condn > 

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In general, it will be like this
 

Use DestinationDB
INSERT INTO dbo.DestinationTable (Column1, Colummn2.. .ColumnN )
SELECT Col1,Col2, ...,NULL
FROM SourceDB.dbo.SourceTable 
<Where Condn > 

Open in new window

0
 
schmir1Author Commented:
Is there any way to do the INSERT without specifying the column names but use column position instead.

If not, is it possible to dynamically get all column names and build the INSERT query with them.

My problem is that I've got thousands of column names scatter over about 100 tables and I need to do this twice, once for test and once with the live production data.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Aneesh RetnakaranDatabase AdministratorCommented:
-- Is there any way to do the INSERT without specifying the column names but use column position instead.
You can do this, but you have to pass the appropriate values on approapriate positions
0
 
schmir1Author Commented:
Can you give me an example?
0
 
schmir1Author Commented:
I'm not very familiar with T-SQL.  Are you saying to put in Col1 etc. or the actual column name?  Col1 comes up with this error:
Invalid column name 'Col1'.

Note: I need some way to do it without manually listed all the column names.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
it should be Either the actual column name  (in case it is to be selected from a table ) or the actual value in case of a constant
0
 
schmir1Author Commented:
That's the problem.  For me to type in the actual column for thousands of columns would take too long.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
That makes it really hard, because, if the column names are the same, that would have been easy with a dynamic sql,
 
0
 
schmir1Author Commented:
Since there aren't that many new columns, I'm going to add columns as needed to make the INSERT work.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.