Here i want to transfer data from one database to another database in sql 2005, i tried in dts but its not working.
1
-
You want to copy data from one database table to another database table?Kashif– Kashif2010-07-15 11:59:01 +00:00Commented Jul 15, 2010 at 11:59
Add a comment
|
3 Answers
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?
Comments
select *
into SecondDatabase.dbo.TableName
from FirstDatabase.dbo.TableName
If you want something else, you have to be more specific.
Comments
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.
