How to Generate Scripts for Data and Schema From a Perticular Table in SQL Server

This is a very common thing if you are an expert in using the SQL Server Management Studio. If you are new then you are going to love it. Let me say you something about the scenario, on which this article is based on.

Let's say you have a table named dbo.JQUser in a database named Company. And you have another table named dbo.JQUser1 inside which you need to insert all the data from the dbo.JQUser table with some slight changes.

In my case I have to insert all the records of dbo.JQUser table in to it once again with some slight changes like a single column value has to be changed and the rest has be same.

So in order to generate an insert script you may need to get all the values and to write down in a sheet and then prepare the insert script there. If you are doing that way then please don't worry SQL Serve got a very useful functionality to save your time and I am going to tell you about it here.

This is really very helpful while creating insert scripts for a table with bunch of data in sql server management studio.

Let's Generate the Scripts For Data and Schema Here   

This article will be a demo about how to generate insert scripts with a pictorial representation. So no need to search for anything just go through the pictures and try it yourself.

Step 1:

Open SQL Server Management Studio and select the database on which you are going to play around.

Now, right click on the database and you will be exposed to a lot of options. Don't panic we are not going to play with all the options. Just hover on the Tasks menu and now you will see another menu window with bunch of options there you need to click on the Generate Scripts... menu.
Finding the Generate Scripts menu in SQL Server
Step 2:

Now you will be presented with wizard to generate the insert scripts. Click the next button of the dialog.
 step 1 of wizard
Step 3:

Now you will be presented with a dialog where you will have 2 radio buttons to select from. Check the second one as we are not going to generate script of the whole database.

After clicking the second option you will be show a tree representing all the tables and stored procedures. Check the table name you want to generate scripts for. Then click next.
Selecting the Table names
Step 4:

In this dialog you will be asked to set the location where you want to save the script. You need to check the first option i.e. Save script to a specific location.

This will have 3 options as below.
  1. Save to file
  2. Save to clipboard
  3. Save to new query window
Select the options according to you, where you want to save the query. Here I will save it to a new query window.
Selecting output type
 Now click on the Advanced button there and you will be presented with another window like below
Select the type of data to script
Here you need to select the type of data you want to script. This will have 3 options like below
  1. Data Only
  2. Schema and data
  3. Schema only
If you wan the data only then select the 1st option, if you want the schema only the select the 3rd option, and if you want both schema and data then click the 2nd option.
Here we will select the 2nd option where we will generate the schema and the insert script also.
Now click the next button.

Step 5:

This is the final step where you have nothing to do rather than clicking 2-3 next buttons only.
Review your selections
Click next button of the above shown dialog. After clicking you will find the below screen.
Generating scripts

 Here you need to click the finish button in order to see your query. Once you clicked on finish you see the below window if you have selected the Save to new query window in step 4.
Query window
Now your insert script along with the schema is ready to use. Go ahead and use it.

I hope you enjoy this article. If find helpful then do share it and help others find it. Sharing option presents both top and below of the page.

Happy Codding.....

No comments:

Post a Comment