Sometimes you may have a table which have populated by business or tech to support the solution you develop. This type of table can be called as Master data table which is not derived from any source system. So whenever you need to move table DDL/script to higher environment (from DEV to UAT or PROD) you need to move not only DDL but also data for that table.
Now let's find out How do you do it?
There are a few ways to do it but my preferable way to do it by using SQL Task, we mostly use this for generating scripts; however, it can also take the data.
Step 1: Select the database and find generate script option
|Fig 1: Finding out generate scripts for the database|
Step 2: Follow the wizard
|Fig 2: Go thought the wizard|
Step 3: Choose the database object, the table you would like to move to the higher environment. And click Next.
|Fig 3: Choose your database object|
Step 4: Before you generate the script, you also need to have data,so click at the 'Advanced' button.
|Fig 4: Advanced scripting options|
Step 5: Change 'Type of data to script' to Schema and data
|Fig 5: get schema and data together|
You are almost done, after completing the addition few steps in the wizard script will be ready with DDL and data. Now you can move the saved file to the upper environment and run the script, you will find table and data together.