how to copy several tables from one server to another including PK, Indexes etc

Question

how to copy several tables from one server to another including PK, Indexes etc

Nishan Ratna 150

Hi All, Is there a way to copy several tables from one server to another server including primary key, indexes etc. I can use transfer sql server objects task in SSIS. However I am not able to automate the process. I want to automate it. Can someone help me please? Thanks

2 answers

Your answer

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 2

ZoeHui-MSFT 41,446

Hi @Nishan Ratna ,

You may use Transfer SQL Server Objects Task in SSIS. The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. For example, the task can copy tables and stored procedures.

When copying tables, you can indicate the types of table-related items to include in the copy process. The following types of items can be copied together with the related table: User's image

A tutorial here, you may take a reference to.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Nishan Ratna 150 Reputation points

2023-04-05T11:25:28.6933333+00:00

Hi Zoe Hui, I want to fully automate the process. That is why I don't want to use transfer sql server object task. I don't want to select the tables manually. I want to pass table names to package so it can loop through one by one. Then create the destination tables dynamically and load the data. How can I do this?
Olaf Helper 46,546 Reputation points

2023-04-05T11:49:29.29+00:00

How can I do this?

Only with a lot of script programming.
Nishan Ratna 150 Reputation points

2023-04-05T14:00:14.26+00:00

Hi Olaf, Do you have a any code to do the job? Thanks
ZoeHui-MSFT 41,446 Reputation points

2023-04-06T01:45:39.24+00:00

Hi @Nishan Ratna , Like Olaf said that if you want to do this fully automatedly, Script task with the C# maybe the way. Code writing is out of my ability, you may raise a new question on Q&A with C# tag for the professional advice.

Share via

how to copy several tables from one server to another including PK, Indexes etc

2 answers

Your answer