Export table data from one SQL Server to another

73

I have two SQL Servers (both 2005 version).

I want to migrate several tables from one to another.

I have tried:

  • On source server I have right clicked on the database, selected Tasks/Generate scripts. The problem is that under Table/View options there is no Script data option.

  • Then I used Script Table As/Create script to generate SQL files in order to create the tables on my destination server. But I still need all the data.

Then I tried using:

SELECT * 
INTO [destination server].[destination database].[dbo].[destination table] 
FROM [source server].[source database].[dbo].[source table]

But I get the error:

Object contains more than the maximum number of prefixes. Maximum is 2.

Can someone please point me to the right solution to my problem?

This question is tagged with sql sql-server sql-server-2005 export

~ Asked on 2012-06-13 06:15:28

The Best Answer is


73

Try this:

  1. create your table on the target server using your scripts from the Script Table As / Create Script step

  2. on the target server, you can then issue a T-SQL statement:

    INSERT INTO dbo.YourTableNameHere
       SELECT *
       FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere
    

This should work just fine.

~ Answered on 2012-06-13 06:24:17


55

Just to show yet another option (for SQL Server 2008 and above):

  1. right-click on Database -> select 'Tasks' -> select 'Generate Scripts'
  2. Select specific database objects you want to copy. Let's say one or more tables. Click Next
  3. Click Advanced and scroll down to 'Types of Data to script' and choose 'Schema and Data'. Click OK
  4. Choose where to save generated script and proceed by clicking Next

~ Answered on 2014-09-30 15:32:14


Most Viewed Questions: