I am new in my environment and Im asked to create scripts for a project. The scripts will migrate (essentially merge) two different tables with their data located on two different databases on two different servers.
All together I have 4 tables. Two on source and two on the target. Thus Table A and Table B are on source Table A2 and Table B2 are on target. Table A on source and Table A2 on target has the same exact columns.
On the other hand Table B on source and Table B2 on target have some similar Columes and some extra. How do I go about starting this script. Any example scripts please?
hese are in a Sql server databases.
Do you have the option of using a tool that handles data migrations?
SSIS (SQL Server Integration Services, part of SQL Server Data Tools) can rather easily create the target table on the target server (Table A2 and/or Table B2) based upon all or selected columns from your source tables from your source server (Table A and/or Table B).
For your Table B2, you can choose some of the columns from Table B as well as add additional columns to Table B2 as you need.
SSIS can also move the data from your source to your target tables, if you desire.
This type of table migration is one of the more simpler operations in SSIS, so there are loads of helpful YouTube videos to help walk you through your first time.