Generate script in SQL Server Management Studio
If I have a table with data in a database in SQL Server, how can I generate a script that will create the table and add the data too?
If I right click on the table then select Script Table As > Create to > File , that generates a script to just create the table without the data. What I need is a script that creates the table and adds the data that is already existing in the table or 2 separate scripts, one that creates the table and one that adds the data.
SQL Server 2014 — SQL Scripts
A discussion on writing SQL scripts and some quick examples.
In the previous lesson, we added data to our database table using the «Edit Top 200 Rows» option. In this lesson, we will look at how to write SQL scripts to update and run queries against our database.
SQL scripts can be used to insert data, read data, update data, and delete data. They can also be used to create database objects such as tables, views, stored procedures, and they can even be used to create whole databases themselves — complete with tables, data, users, etc.
Transact-SQL
SQL Server supports Transact-SQL as a scripting language. Transact-SQL is based on SQL (Structured Query Language), which is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it. Whenever I refer to a SQL script here, I mean a Transact-SQL script.
SQL Statements
SQL scripts are usually made up of one or more «statements». Each statement tells SQL Server what to do.
SQL scripts can contain many statements. For example, a SQL script could contain a statement to CREATE a table, another statement to INSERT data into that table, and another statement to SELECT all the data that was just inserted into the table. In fact, SQL has specific statements that are used for those exact three things: the CREATE statement, the INSERT statement, and the SELECT statement.
Generally speaking, your statements will begin with the word of the task that you’re trying to perform. If you’re trying to create a database object, your statement will start with CREATE. I say «generally speaking» because, there are some other bits and pieces that you can throw in to a SQL statement that may come prior to your statement.
Example SQL Statement
Probably the easiest SQL statement to learn is the SELECT statement. Here’s an example of a SELECT statement at its simplest:
The above statement selects all columns from the Tasks table. The asterisk ( * ) means «all columns». The above statement could be modified slightly to only return a particular column, and we could add a WHERE clause to filter the data to only those records that we’re interested in:
The above statement selects the TaskName column from the Task table, but it only returns those that have a StatusId of 3 . The value of 3 could mean «To Do» or «Done» or whatever it is we want it to mean. In our TaskTracker database, we’ll be creating a new table called Status where we’ll specify what «3» means (and «1» and «2» of course). These numbers are simply the values in the StatusId field (which we specified as an Identity column — an autonumber). Each of those numbers will have a corresponding value in the StatusName field which will tell us what status it actually is.
Then, once we’ve created our Status table, we could modify the above SQL statement to include the Status table so that we can write WHERE StatusName = «To Do» instead of trying to remember what number «To Do» is.
But we need to create our Status table first.
Creating Tables with a SQL Script
Below is a SQL script that will another table on our database — a Status table. If you aren’t familiar with SQL it might look a bit weird. But I’m sure you’ll have some idea of what it does when you see the names of the columns and their corresponding data types. In this script, we aren’t allowing any NULL fields (hence the NOT NULL next to each column). We’re also creating a primary key for the StatusId field, and we’re setting a default value to the DateCreated field to (getdate()) .
To run this script, do the following:
- Copy the script to your clipboard
- In the SQL Server Management Studio, click the New Query button on the toolbar:
- Paste the script into the query window
- Click the ! Execute button on the toolbar:
Once the script has run, you should see a message that reads Command(s) completed successfully.
Here’s the script:
Keyboard Shortcut
You can also run a query by pressing F5 on your keyboard.
Plus, you can even run part of a query by selecting the part that you want to run, then pressing F5 . This works well on larger scripts that contain a lot of SQL statements, but when for some reason, you only want to run one or two (or even run all of them but just one at a time).
Add Data via SQL Script
You can also add data via a SQL script. The script will use the INSERT statement to insert data into the tables that you specify. You can have the script insert data into all columns in the table, or just those that you specify.
The following script inserts data into the Status table, and then selects that data (so we can see that it went in). Run this script the same way you did with the above script):
And here’s what that looks like:
As you can see, the results of the query are displayed in the bottom pane.
Cross Reference Data
Now that we’ve got our Status table, let’s add a value to the StatusId field of the Tasks table (remember we left that field blank for all records because we didn’t yet have the Status table).
So the value we add is going to link the Tasks table to the Status table. Each record in the Tasks table will now have a StatusId which will be either 1, 2 or 3 (to match a value in the StatusId field of the Status table).
To do this, we’ll need to use an UPDATE statement (because we’re updating the records rather than inserting new ones).
So without further ado, let’s run the following script:
And you should now see your Tasks table, complete with its StatusId field populated for all records. Something like this:
Combining Scripts
Actually, we could just have easily combined all of the above scripts and run them as one. I only kept them separate in order to make it easier to understand which part does what.
If you’d like to learn more about creating SQL statements, check out my SQL tutorial.
Case Sensitivity
SQL is case insensitive. So the following statements all mean the same thing:
- SELECT * FROM TASKS
- SELECT * FROM Tasks
- Select * From Tasks
- select * from tasks
Database Administration
Most of the database administration tasks (such as creating users, backups, etc) that can be performed in SSMS via the graphical user interface can be performed programatically via SQL scripts. This tutorial concentrates on using the graphical user interface, mainly because it’s usually a lot easier for new users to get their head around. Once you become more familiar with SQL Server, you may find yourself using SQL scripts to perform many of the tasks that you started out doing via the graphical user interface.
progtask.ru
Как из таблицы сделать INSERT скрипты в MS SQL SERVER
Для создания INSERT скриптов нужно выполнить следующую последовательность действий.
Зайти в SSMS (SQL Server Management Studio) и нажать правой кнопкой мыши по требуемой БД, потом перейти в раздел Tasks и выбрать пункт меню «Generate Scripts…».
Затем в появившемся окне выбрать пункт «Choose Objects».
После выбирайте пункт меню «Script entire database and all database objects» если вам нужны все таблицы, либо «Select specific database objects», если только определённые.
Далее в списке слева выбираем пункт «Set Scripting Options» и жмём на кнопку «Advanced».
В появившемся окне ставим атрибуту «Types of data to script» значение «Data only».
После этого подтверждаем изменение атрибута и указываем путь к файлу, в который будут генерироваться строки sql кода.
Generate Database Scripts With Data In SQL Server
Join the DZone community and get the full member experience.
In this post we will discuss how we can generate SQL Server scripts of our database with existing data, once we generate the script, the result will give you an insert query with other queries so that the data also will get inserted in your new database. We are going to use the option ‘Schema and data’ in SQL server for this task. I hope the screenshots given will help you generate the script with insert queries.
Background
I was working on an article that has so many database transactions/actions. So I thought of attaching the database scripts with data in the source code download section of the article, so that it may help the user to insert the data in his/her database. That made me write this article. I hope you will like this.
Steps to Generate Database Scripts With Data In SQL Server
Step 1
Right-click on your database and select Task -> generate script.
Step 2
Click next in the introduction screen.
Step 3
Select the database object which you are all you need and then click next.
Step 4
Now you will be shown a window which asks you about how your script should be published.
Click advanced in that window.
Step 5
Select ‘Schema and data’ from type of data to script option and then click OK.
Step 6
Click finish, now check the script file, it must be having the insert queries too.
Now what else is pending, go ahead and run your script.
Conclusion
Did I miss anything that you may think which is needed? Have you ever wanted to do this requirement? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.
Your Turn. What Do You Think?
A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can. And you can find more of my thoughts on SQL here:
Published at DZone with permission of Sibeesh Venu , DZone MVB . See the original article here.