How do I copy several records from one server to another?

Question

Post reply

How do I copy several records from one server to another?

Rod at work

SSC-Dedicated

Points: 34008
More actions
September 11, 2015 at 5:22 am

#304491

I've got to copy several records (almost a 100) in a table from one instance of SQL Server 2008 R2, to another instance of SQL Server 2008 R2, but on a different server. The table structure is identical. I've searched online and found examples of doing a bulk insert into a table from a .CSV file, so I'm guessing that would be a good way to go about doing it. However I don't know of a way of exporting records to a .CSV file using a SELECT statement. So how can I get that done?
As a quick aside, I've heard of linked servers, but at least as far as I know linking the server would require administrative privileges that I don't have on either machine. If I'm wrong, then I'd be willing to give that a try.
Kindest Regards, Rod Connect with me on LinkedIn.
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]

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

If you can create a Linked Server then you could do:

INSERT INTO dbo.MyTable

SELECT *

FROM RemoteServer.RemoteDatabse.dbo.RemoteTable

WHERE ...

if not then I would use BCP to produce a file containing the records. BCP can both export-to-file and import-from-file. I would absolutely not use CSV 🙂 BCP has a "native data format mode" which will preserve the data nicely, including any embedded commas. tabs. line breaks etc.

You could probably use SSIS instead of BCP, but I'm an old dinosaur so BCP is all I know about ...

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

If this is a one-time activity, you can query the rows in SSMS and save them as a CSV file by right-clicking the results pane...Save As and select CSV. If this is something you're going to be doing regularly, you probably don't want a manual process like this. An automated process would be best.

Your DBA can help you set up a linked server so you can insert the rows directly from one server to the other.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

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

I would use the Import/Export wizard that will create an SSIS package that you can save for future executions or dismiss after running it once.

It allows you to use queries to export the data that you want and you'll work with your own credentials.

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

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 4

Ed Wagner (9/11/2015)
If this is a one-time activity, you can query the rows in SSMS and save them as a CSV file by right-clicking the results pane...Save As and select CSV. If this is something you're going to be doing regularly, you probably don't want a manual process like this. An automated process would be best.
Your DBA can help you set up a linked server so you can insert the rows directly from one server to the other.

This is a one-shot thing. I don't expect I'll have to do this again after this. Thanks.

Kindest Regards, Rod Connect with me on LinkedIn.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Rod at work (9/11/2015)
Ed Wagner (9/11/2015)
If this is a one-time activity, you can query the rows in SSMS and save them as a CSV file by right-clicking the results pane...Save As and select CSV. If this is something you're going to be doing regularly, you probably don't want a manual process like this. An automated process would be best.
Your DBA can help you set up a linked server so you can insert the rows directly from one server to the other.
This is a one-shot thing. I don't expect I'll have to do this again after this. Thanks.

Glad I could help. Thanks for the feedback.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Rod at work (9/11/2015)
Ed Wagner (9/11/2015)
If this is a one-time activity, you can query the rows in SSMS and save them as a CSV file by right-clicking the results pane...Save As and select CSV. If this is something you're going to be doing regularly, you probably don't want a manual process like this. An automated process would be best.
Your DBA can help you set up a linked server so you can insert the rows directly from one server to the other.
This is a one-shot thing. I don't expect I'll have to do this again after this. Thanks.

If I had a quid for every time I heard this...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 7

Ok Ed, I've got a question and an observation.

First the observation. I didn't know it when I wrote the SQL BULK INSERT script, but that must be run on the server. I tried running it from my dev box. Nothing doing. So I copied it to the server and I can run it there.

Now for the question. The first column in the table is an identify column. I had thought the BULK INSERT would "know" enough to ignore trying to insert the data into that first column and just insert the rest of the columns, which I included when I did the SELECT. Well, it doesn't. So, will a BULK INSERT not work? Am I going to have to try something else?

Kindest Regards, Rod Connect with me on LinkedIn.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Rod at work (9/11/2015)
Ok Ed, I've got a question and an observation.
First the observation. I didn't know it when I wrote the SQL BULK INSERT script, but that must be run on the server. I tried running it from my dev box. Nothing doing. So I copied it to the server and I can run it there.
Now for the question. The first column in the table is an identify column. I had thought the BULK INSERT would "know" enough to ignore trying to insert the data into that first column and just insert the rest of the columns, which I included when I did the SELECT. Well, it doesn't. So, will a BULK INSERT not work? Am I going to have to try something else?

In your format file, you specify the source field and destination column. Simply don't specify a destination for the identity column.

If you know your data file contains the identity column values you want to keep, you can fire the following before doing the insert.

set identity_insert [table_name] on;

Please don't forget to turn it back off when you're done. Also, I believe you can have it turned on for only one table at a time in a single session.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 9

Would that format file be my .CSV file? And the source and destination columns would be the first row of the .CSV file? If so, then I'd better go back and get those column names and put them into the first row (they're not there now).

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 10

OK I've found out what a format file is, and have created it using the bcp utility. (Never done that before.)

Now what I don't know how to do is not specify a destination in the receiving database. Or as you put it, how to not specify a destination column for the identity column.

Kindest Regards, Rod Connect with me on LinkedIn.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

If this is a one time copy of only ~100 rows, then why go to all the bother of scripting/exporting and importing?

Right click your source table in SSMS, and choose Edit top xx rows.

Click the little "sql" icon in the toolbar to display the query and edit it to remove the identity column, then refresh the table by clicking the "!" button or pressing CTRL-R

Add a where clause if required to limit to just the rows you want.

You can now click the row header of row 1, scroll to the bottom and shift-click the row header for the last row of data (not the very bottom row, which is for adding new rows) to highlight all the required rows and press CTRL-C to copy.

Open the table on the second server for editing, adjust the SQL again to remove the identity column and refresh the table.

Now, click on the row header for the very last row (the one for creating new rows) and CTRL-V.

You have now copied the rows to the other server.

MM

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