SQLServer T-SQL prodecure to copy table from Server 1 DB "A" to Server 2 DB "B"

On Server A (Origin of the data) I haven't got full rights because its a hosted shared server. Namely I can't DUMP data.

I can do the job within SQLServer Manager, creating statements one table at a time, using a linkedServer on one end and t开发者_C百科he local server on the other and supplying the name of each column... Phew! Tough job!

By the way many tables have Identity primary keys.

What I would like (ideally) is a T-SQL procedure that will take a few bunch of parameters like the name of linked server, the original table name and:

1 - Create the destination table

2 - Copy the data

Of course its up to me to do this in the correct order with due respect to foreign keys.

Before writing any code to manage this take you should check out 3rd party tools that may meet your needs. Red Gate Software, as one example, provides several utilities with which you can copy the structure of a database and then merge the data. I believe that all of your requirements are met by two RG applications. Now, Red gate isn't free but does have a full trial license. There are probably free/open source tools out there as well.