1,105,578 Community Members

Importing SQL Tables from one database to another

Member Avatar
mayzebra
Newbie Poster
2 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I'm trying to find information on how to import selected data from one SQL database to another using C# (or any language for that matter) or the cmd line. aka: I dont want to use the wizard.

I could export to a CSV file, then import... but that seems needslessly complicated and (run) time consuming. Any suggestions?

Member Avatar
AmirBedair
Light Poster
26 posts since Jul 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Dear mayzebra ,

if you need to make copy (x) tables in database 1 to database 2 with same data.

you can better make a database replication.
check this link
http://technet.microsoft.com/en-us/library/bb693697(TechNet.10).aspx


but if you want to do this by code to make changes in data or specific data to be moved..

first solution : If the 2 database in same server

you can make stored procedure in SQL:
with command
like this:

insert into database1.dbo.table1 (col1,col2,col3)
select col4,col5,col6 from database2.dbo.table2
where (where condation)

If I don't understand your questions, explain more, and I will help you as I can

Member Avatar
FaridMasood
Junior Poster in Training
59 posts since Mar 2007
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Here is the command prompt commands to generate hte script of database tables and the data in these tables.

But to use these commands you have to download and install "DatabasePublishingWizard" this will create the "sqlpubwiz.exe" at your installation directory. This tool is not installed if you are using the sqlexpress 2005

Command to run which will create schema and database:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql”

Command to run which will create schema:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -schemaonly

Command to run which will create data:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -dataonly

Member Avatar
tharakaw
Newbie Poster
1 post since Aug 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Dear mayzebra ,

if you need to make copy (x) tables in database 1 to database 2 with same data.

you can better make a database replication.
check this link
http://technet.microsoft.com/en-us/library/bb693697(TechNet.10).aspx


but if you want to do this by code to make changes in data or specific data to be moved..

first solution : If the 2 database in same server

you can make stored procedure in SQL:
with command
like this:

insert into database1.dbo.table1 (col1,col2,col3)
select col4,col5,col6 from database2.dbo.table2
where (where condation)

If I don't understand your questions, explain more, and I will help you as I can

hi Amir,

how to do the same, if the databases are in two different servers

Member Avatar
AmirBedair
Light Poster
26 posts since Jul 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Dear tharakaw

Please read MSDN help and check all the child pages from how to configure and make linked server :
http://msdn.microsoft.com/en-us/library/ms188279.aspx

regards,
Amir

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article