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?

Solution 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.

Solution 2:

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

Solution 3:

There is script table option in Tasks/Generate scripts! I also missed it at beginning! But you can generate insert scripts there (very nice feature, but in very un-intuitive place).

When you get to step "Set Scripting Options" go to "Advanced" tab.

Steps described here (pictures can understand, but i do write in latvian there).