How to transfer data from one database to another database in sql 2005

Question

Hi guys, Here i want to transfer data from one database to another database in sql 2005, i tried in dts but its not working.

Answer 1

Need more information, but if you want to just copy a database, you can back it up, then restore that backup in another database. If you just want to copy individual tables then DTS is your friend. How is it "not working" for you?

Answer 2

select *
into SecondDatabase.dbo.TableName
from FirstDatabase.dbo.TableName

If you want something else, you have to be more specific.

Answer 3

If you're moving a few tables once off then the simplest way is to use the BCP command line utility.

bcp db_name.schema_name.table_name out table_name.dat -c -t, -S source_server -T bcp db_name.schema_name.table_name in table_name.dat -c -t, -S destination_server -T

Change '-T' to '-U your_username -P your_password' if you're not using trusted connections.

If you're moving data regularly between servers on a LAN then consider using linked servers. http://msdn.microsoft.com/en-us/library/ff772782.aspx

Link server performance over WANs is often poor, in my experience. Consider doing a BCP out, secure file transfer to the destination server then BCP in if the servers aren't on the same LAN.