Copy huge table from one server to another

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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 · Answer 2

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

Thanks

SQLBill SSC Guru Points: 51440 More actions · Answer 3

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