Solved

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

Posted on 2009-05-12
10
208 Views
Last Modified: 2012-05-06
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?

0
Comment
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24365683
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
 

Author Comment

by:schmir1
ID: 24366012
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24366153
-- 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:schmir1
ID: 24366246
Can you give me an example?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24366280

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
 

Author Comment

by:schmir1
ID: 24367677
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24367745
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
 

Author Comment

by:schmir1
ID: 24367761
That's the problem.  For me to type in the actual column for thousands of columns would take too long.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24367835
That makes it really hard, because, if the column names are the same, that would have been easy with a dynamic sql,
 
0
 

Author Comment

by:schmir1
ID: 24390046
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

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question