[sql] Export table data from one SQL Server to another

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:

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 related to sql sql-server sql-server-2005 export

The answer is

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.

Similar questions with sql tag:

Similar questions with sql-server tag:

Similar questions with sql-server-2005 tag:

Similar questions with export tag: