Export table data from one SQL Server to another
Try this:
create your table on the target server using your scripts from the
Script Table As / Create Script
stepon 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.
Just to show yet another option (for SQL Server 2008 and above):
- right-click on Database -> select 'Tasks' -> select 'Generate Scripts'
- Select specific database objects you want to copy. Let's say one or more tables. Click Next
- Click Advanced and scroll down to 'Types of Data to script' and choose 'Schema and Data'. Click OK
- Choose where to save generated script and proceed by clicking Next
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).