Export SQL data to a file

Question

Export SQL data to a file

ajreynolds

SSC Enthusiast

Points: 166
More actions
February 20, 2013 at 1:11 pm

#270649

I occasionally have need to export data from a MSSQL database to a file, copy the file to another server on a different network, then import the data from the file into a different MSSQL database. In the past I've done this by exporting the data to an Access database file, but on newer machines with Office 2010, Access isn't included. Sometimes I need to do large amounts of data from many tables, so using a flat file or Excel wouldn't work well. What would be the recommended way to handle this sort of thing?
Thanks,
Andy
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

sturner SSC-Insane Points: 22411 More actions · Answer 1

You should use bcp utility. It is included as apart of every SQL Server installation. It is a command line utility, you specify what data is to be exported via command line parameters to a given file, then you can use bcp to bulk insert the same file to a table on the destination server.

There are lots of command line option and you can look up the details in BOL but in the simplest case, going from SQL server to SQL Server you can use:

output:

bcp "select query" queryout destFilePath -k -N -Sserver -T

input at destination:

bcp dbname.dbo.tablename in destFilePath -E -k -N -Sserver -T

The probability of survival is inversely proportional to the angle of arrival.

Scott D. Jacobson SSCertifiable Points: 6089 More actions · Answer 2

Depending on what is meant by "copy the file to another server on a different network", bcp may run into permissions issues.

I'd take a look at using SSIS for this and make a package to do the work for you. It may have a steeper learning curve than BCP but it can be more flexible.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

ajreynolds (2/20/2013)
I occasionally have need to export data from a MSSQL database to a file, copy the file to another server on a different network, then import the data from the file into a different MSSQL database. In the past I've done this by exporting the data to an Access database file, but on newer machines with Office 2010, Access isn't included. Sometimes I need to do large amounts of data from many tables, so using a flat file or Excel wouldn't work well. What would be the recommended way to handle this sort of thing?
Thanks,
Andy

create a new database

copy the tables to it

detach it/back it up

copy it to remote server

attach it/restore it

copy data to desired location

This can of course be scripted as well, just to make life simpler.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);