2

I have a need to bulk insert circa 1m rows from one SQL Server (A) to another (B) on a daily basis.

Server A hosts the records, and has access to Server B as a linked server. Both are SQL Server 2008 Express editions. Due to internal political/governance reasons that make life difficult, I cannot setup Server A as a linked server in Server B, making the SELECT INTO option not possible.

So I'm stuck trying to find a way to push the data across in the best possible time - an INSERT INTO statement takes approx 20 minutes to execute. The table I'm pushing into has no indexes whatsoever to avoid further slow down from there.

BULK INSERT and BCP don't seem applicable in this instance. A post I read somewhere alluded to doing a INSERT INTO with OPENROWSET BULK, but I haven't found a way to do that. Somewhere else mentioned committing it one transaction but I haven't had much luck researching that either.

Does anyone know how can I accomplish this in a better way?

Ryan Gillies
  • 413
  • 4
  • 8
  • 19

1 Answers1

0

It isn't completely clear what restrictions you have or why the options you've already mentioned aren't suitable, but here are some things to try:

  • OPENROWSET gives (more or less) the same functionality as a linked server but it's ad hoc with all connection information in the query so this may be a workaround for the governance issues you mentioned
  • Use bcp.exe to export the data to a flat file (preferably in native format), then copy it to server B and import it with bcp.exe or BULK INSERT
  • Write a script or program that queries the data from server A and inserts it into server B (perhaps using the SqlBulkCopy API)

And of course there are dozens of questions on this site already about copying data to another database and/or server.

Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51