Saturday, August 17, 2019

Published on Saturday, September 05, 2015

Copy a Table from One Database to Another on Different Clients

So, I have database - let's call it R2 - with a table called [dbo].[Links] that has content that I want to copy as is to an identically designed and named table in another database - let's call it D2. I thought that maybe this would work:

INSERT INTO [D2].[dbo].[Links]
SELECT * FROM [R2].[dbo].[Links]

No go! Didn't like two things: (a) writing into the read-only ID field; (b) R2 and D2 have different credentials and even though I was logged in to both at the same time in the same instance of SMS, the SQL statement didn't accept this as being privileged enough. I tried copying individual columns from R2, leaving out the ID column, to the equivalent columns in D2, which solved problem (a) apparently, but still there was a permission problem.

What I needed to do was export the table from R2 and import it into D2. This seemed a lot of work, though, because it would mean FTPing CSV files back and forth between my hosted platform and my local machine. Not the end of the world, but inelegant, I thought, especially since I was logged into both of my databases simultaneously in a single instance of SMS. I figured there had to be a way of getting the data across from one to the other. There was! It wasn't altogether pretty, but hey, it worked! Here's what I did.

In SMS I right-clicked on R2 and selected the Export option. In the pop-up I provided the credentials for R2 and clicked Next. In the next window I provided the credentials of D2 on the same server, and selected the correct database from the list, then clicked Next. On the next screen I accepted the default of "Copy data from one or more tables or views". I then selected the table I wanted to copy from the list of tables in the R2 database (Source), as displayed. It automatically populated the right-hand field (Destination) with the same table name - i.e. it intelligently thought it could append the data from one table to the other because it detected that they were identically set up. (That didn't work, by the way... I tried every permutation of that and failed with permission errors every time!) Here's the important bit... click on the table name in the Destination column and change its name - I just called it [dbo].[Links2]. If you now click on the "Edit Mappings..." button, it no longer says it is going to append the data (because the table doesn't exist now), so it says it will create destination table. Excellent! Just follow that through now and eventually click "Finish", whereupon it will happily create a new table in D2 (quite happy with the permissions now!)

Once you have the table in D2, you can now use the above SQL statement to INSERT INTO the original table. I.e. something like this:

INSERT INTO [D2].[dbo].[Links] (col3, col4, col5, ...)
SELECT col3, col4, col5, ... FROM [R2].[dbo].[Links]

Actually, I added a WHERE clause to that because I didn't want it to copy ALL the contents of the old table to the new one! Also, I had to do some trickery with one of the fields. The "ModuleID" (col2, which I initially omitted, along with the ID column, col1) field was populated with the IDs of the R2 database modules, which didn't exist in the D2 database, so it came back with errors; when I tried to exclude this column it came back with errors saying that ModuleID cannot be NULL, so I edited the design of the table and allowed it to accept NULL, re-ran my SQL script (it worked!), and then changed it back to no NULL afterwards. A better way would have been just to include the column (col2) in the INSERT INTO part, and in the SELECT part just put a single integer being the ID of a module that does exist in the D2 database.

All that remained was to tidy up by deleting the "copied" table that was now no longer needed in D2. I had successfully combined what I wanted to in the two databases, ending up with R2D2... the force was with me!

Comments (0)Number of views (1339)

Author: Exweeto

Categories: Database, MSSQL



Please login or register to post comments.

“If I had eight hours to chop down a tree, I'd spend the first six of them sharpening my axe.”

Abraham Lincoln

Copyright 2019 by Exweeto Terms Of Use Privacy Statement