SQL Server Performance Forum – Threads Archive

best approach of transfering tables

Hi everyone. I need some help on a sql script that will not impact the source server and the target server. I have 6 tables that I need to transfer for data validation purposes. Roughly, all 6 tables have 20+ million records and has many columns too. What is the best way to transfer those 6 tables from serverA to serverB? Thanks, V

I think DTS or Import/Export wizard will be faster Madhivanan Failing to plan is Planning to fail

Hi,
these can be acheive using select into statements also….
and further more assistance look in the BOL for
"Copying Data Between Servers"
HTH
Regards.
hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

In this case I would go with BCP and BULK insert for ease and speed of operations. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Ok, I’m gonna read up on it. I’ve used BCP but for pulling data from a text file. I’m gonna to lookup at the syntax on how it reads from another table. Thanks! <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />

Hi BCP OUT Statement is used for the same have a look at the following article for BCP Statement : <a href=’http://www.sql-server-performance.com/bcp.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/bcp.asp'<a target="_blank" href=http://www.sql-server-performance.com/bcp.asp>http://www.sql-server-performance.com/bcp.asp</a></a> <a href=’http://support.microsoft.com/default.aspx?scid=kb;en-us;81339′ target=’_blank’ title=’http://support.microsoft.com/default.aspx?scid=kb;en-us;81339′<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;81339>http://support.microsoft.com/default.aspx?scid=kb;en-us;81339</a></a> <a href=’http://support.microsoft.com/default.aspx?scid=kb;en-us;153131′ target=’_blank’ title=’http://support.microsoft.com/default.aspx?scid=kb;en-us;153131′<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;153131>http://support.microsoft.com/default.aspx?scid=kb;en-us;153131</a></a> you should also read following articles : FAQ on BCP <a href=’http://support.microsoft.com/default.aspx?scid=kb;en-us;67409′ target=’_blank’ title=’http://support.microsoft.com/default.aspx?scid=kb;en-us;67409′<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;67409>http://support.microsoft.com/default.aspx?scid=kb;en-us;67409</a></a> BCP and Transaction Logging <a href=’http://support.microsoft.com/default.aspx?scid=kb;en-us;46356′ target=’_blank’ title=’http://support.microsoft.com/default.aspx?scid=kb;en-us;46356′<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;46356>http://support.microsoft.com/default.aspx?scid=kb;en-us;46356</a></a> BCP and NULL Values <a href=’http://support.microsoft.com/default.aspx?scid=kb;en-us;98620′ target=’_blank’ title=’http://support.microsoft.com/default.aspx?scid=kb;en-us;98620′<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;98620>http://support.microsoft.com/default.aspx?scid=kb;en-us;98620</a></a> and more on BOL [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] HTH Regards. hsGoswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

http://databasejournal.com/features/mssql/article.php/3391761
http://www.windowsitpro.com/SQLServer/Article/ArticleID/19760/19760.html
For your amusement. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I would use DTS. Should be faster than BCP. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

I think BCP is better than DTS as it will use bulk copy operations and BCP is always higher hand when dealing with huge data. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Can you imagine, I believe I’ve read this in some Ken Henderson book that BULK INSERT is sometimes multiples faster than BCP, because it use OLEDB, but I can’t find the reference right now? [<img src=’/community/emoticons/emotion-6.gif’ alt=’

]]>