Today I had a table in one database instance that I needed to copy into another.
I knew I could use “INSERT INTO SELECT ….” for a database in the same instance, but that it wouldn’t “just work” for a database in another instance.
First I tried generating scripts via Sql Server Management Studio 2008’s Tasks/Generate Scripts command including the Schema and the Data.
The script file was over 5 gigabytes and couldn’t be loaded. I tried splitting it up into 1Gb chunks using Hex Editor Neo (a great tool I found today that can happily open and work on really big files), but the scripts still couldn’t be loaded in Sql Server Management Studio .
I started Googling around a bit and found that I could use the “Linked Servers” feature. This is located under the “Server Objects” item under the Server Connection’s root in Sql Server Management Studio. I was simply connecting from one instance to another on the same machine, but it was tricky to get the permissions right. I was connecting from one instance with SQL Server authentication to another using Windows Authentication.
I created a “New Linked Server” in “Linked Servers”. The settings I found that I needed were:
- under the “General” settings, the “Linked Server” needs to be HOSTNAME\INSTANCENAME
- Server type is “SQL Server”
- under the “Security” settings, you need to select the local login name as HOSTNAME\USERNAME and select the “Impersonate” check box
- Under “For a login not defined in the list above, connections will:” select “Be made using the login’s current security context”
That was it, but as the error messages were particularly unhelpful, it is worth having this checklist.
Once you’ve got the linked server, to copy the data just execute:
SELECT * INTO [dbo].[NEWTABLENAME] FROM [HOSTNAME\INSTANCENAME].[DATABASENAME].[dbo].[TABLENAME]
Where HOSTNAME\INSTANCENAME is exactly what you entered as the “Linked Server”.
When you run this, it will run in a bulk mode and is surprisingly quick. The data that was represented in my 5GB script was copied over in a few minutes. Job Done.