Export table data from one SQL Server to another

Question

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?

Answer 1

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.