Importing SQL Tables from one database to another

[mayzebra]

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?

[AmirBedair]

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

[FaridMasood]

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

[tharakaw]

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

[AmirBedair]

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