SQL - transferring data to another database

[puterkrazy]

I need to transfer data to another database, what are SQL commands to do this and what is the best way?

 

[magnificent1]

If you are using SQL server then use the databse copy or do a back up and restore of the database you are trying to move. You can also create the database and import the data from your original database to the target database.

 

[puterkrazy]

sorry i was not clear at all... i need to transfer data programmatically, during runtime. what are commands to do this? i know little about load/unload, is there something better?

 

[JamesLean]

Using a simple INSERT...SELECT query would probably be easiest, depending on your situation. Can you provide more detail:

Is the DB on the same server?
Do the target tables already exist?
Do you need to scrub the data before transferring?
etc..

--James

 

[puterkrazy]

The tables do not exist and the DB may not be on the same server. How do you connect to a different database?

 

[JamesLean]

If the target DB is on a different server, you just need to set it up as a linked server (look this up in BOL). Then you can refer to it using:

SELECT * FROM server.dbname.owner.table

--James

 

[puterkrazy]

thank you that is helpful! how do you transfer the data then?

 

[JamesLean]

If the table exists then use a simple INSERT command:

INSERT server2.db1.dbo.t1 (col1, col2, col3)
SELECT col1, col2, col3
FROM t1

If the table doesn't exist, you can either run a CREATE TABLE command first, or use SELECT INTO:

SELECT col1, col2, col3
  INTO server2.db1.dbo.t1
FROM t1

--James

 

[puterkrazy]

how can i create a table on another server?

 

[puterkrazy]

help please!?

 

[ecobb]

The code JamesLean gave you will create the table automatically.

On the database WITH the information you want to transfer, run a statement like this:

SELECT col1, col2, col3
INTO server2.db1.dbo.t1
FROM t1

(if you want to transfer the entire table, you can do this)

SELECT *
INTO server2.db1.dbo.t1
FROM t1

Change INTO server2.db1.dbo.t1 to the server.database.owner.tablename you want to create, and change FROM t1 to whatever table you're transferring the data from. SQL Server will automatically create the table specifed in the INTO server2.db1.dbo.t1 and populate it whith the data from FROM t1.

Hope this Helps!

 

[puterkrazy]

I am still getting the following error:

Server: Msg 117, Level 15, State 1, Line 3
The object name 'server.db.dbo.' contains more than the maximum number of prefixes. The maximum is 2.


help please!

 

[arlesaraujo]

How about creating a DTS package (via the DTS wizard). Simply select your source and destination and then select all the tables you want to copy over. If you want the "SQL Select" statement, in the "Select Source Tables and Views" screen click the "Transform" button and in the "Column Mappings and Transformations" window click "Edit SQL", this will give you the exact SQL statement to copy your tables to another location, you can simply copy-paste it.

If you need the VBS code, go to the "Save, schedule, and replicate package" screen (screen after "Select Source Tables and Views&quot and check "Save DTS Package" and then check "Visual Basic", this will ask you for a file name and location to save the VBS code at the end of the wizard screen.

I hope this helps...

Arles

 

[ecobb]

Are both databases on the same server? If so, drop the 'server.' from the 'server.db.dbo.tablename'

Hope this helps!

 

[puterkrazy]

Arles thank you, that was a good idea. Unfortunately the SQL code creates the create table without the server specified! Ugh!

The databases are on different servers...