Copy huge table from one server to another

• rash3554

  SSCommitted

  Points: 1984

  More actions

  July 12, 2016 at 11:08 am

  #327617

  Hi there,

  I have a table with 500 million records on server1

  Create table dbo.temp(ID INT not null,Type varchar(50) not null, Value real not null,URL varchar(max), InsertedDate smalldatetime null)

  I want to copy over this to another database in a different server over the network but selected columns

  SELECT ID,Type,Value FROm info.dbo.temp to [server2].[Info].dbo.copiedtemp

  I also want to add a new column with todays date with Date only column

  INSERT INTO info.dbo.copiedtemp(1,'N','0.234','2016-07-12')

  This is what I did

  1. Used SQL Server Export wizard with query

  SELECT ID,Type,Value FROm dbo.temp to dbo.copiedtemp

  to a text file

  2.Using text file Imported to another server/table

  3.Alter table dbo.copiedtemp Add createdDate Date Not Null default '2016-07-09'

  4. ALTER TABLE DROP CONSTRAINT ConstraintName

  I know there are different ways of accomplishing this. I want to know your thoughts on what's the best way of doing this.

  And also, if I want to automate this every week. what's the best way to do this?

  Thanks

  Rs

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 12, 2016 at 12:18 pm

  #1889691

  You should try using bcp, it should use less resources to accomplish what you're doing. 😉

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• rash3554

  SSCommitted

  Points: 1984

  More actions

  July 12, 2016 at 6:17 pm

  #1889747

  Can you please elaborate on "less resources" ..not sure about log files also?

  Thanks

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  July 12, 2016 at 9:45 pm

  #1889756

  Here's an example of using BCP:

  use info

  go

  declare @sql varchar(2000), @command varchar(2000)

  set @sql = '

  SELECT ID,Type,Value, GETDATE() FROm info.dbo.temp'

  set @command = 'bcp "'+@sql + '" queryout \\servername\drive:\info_temp.data -T -c -b10000'

  print @command

  --exec master.dbo.xp_cmdshell @command

  declare @sql varchar(2000), @command varchar(2000)

  set @command = 'bcp Info.dbo.copiedtemp in \\servername\drive:\info_temp.data -T -E -b10000 -n'

  print @command

  --exec master.dbo.xp_cmdshell @command

  I always use the PRINT option to see what the code will look like before I run it. When you use BCP, the destination table must already exist and have the appropriate columns. In this one, I used GETDATE() in the BCP out to show one way to get the Date/Time that you mentioned. You would just need to replace servername with the actual name of the server and drive with the actual drive letter where you want the BCP file to end up at.

  -SQLBill