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

[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:

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

Try this:

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

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.