Insert Data in SQL Server 2017
How to insert data into a SQL Server 2017 database using SQL Operations Studio (now called Azure Data Studio).
There are many ways to insert data into a database. The method you use will largely depend on the type of data, as well as the context with which it's being inserted. Let's start off by inserting data using a SQL script.
Insert Data by SQL Script
You can run a script that contains all data to be inserted. This is made possible with the SQL INSERT statement.
Run the following script against the Music database that we created earlier:
The above script inserts data into the Artists table. It inserts a new row for each artist.
Now run the following statement:
That statement returns the contents of the Artists table:
You'll see that the ArtistId column contains data even though we didn't actually add any data to that column. This is because ArtistId is an identity column (we used IDENTITY(1,1) in the table definition when we created the column). The identity column automatically inserts an incrementing value with each row, so we don't need to insert a value for that column — SQL Server does that for us. We also set this column to be the primary key when we created it, so each value is sure to be unique.
Now run the following script:
That script inserts data into the other two tables so that we'll have some data to work with later when we run some queries.
Direct Input
Some GUI tools allow you to enter data directly into the table via the interface. Like this:
-
Open Table in Edit Mode
Navigate to the table you want to enter data into.
Right-click on the table and select Edit Data (or whatever your GUI tool calls it — SSMS calls it Edit Top 200 Rows).
-
Enter Data
The table will open, allowing you to type data directly into the cells.
Other Methods
Here are some other methods that can be used to populate a database with data. Whether or not you're able to do these will depend on your database management tools and/or environment.
-
Copy/paste
If you have a GUI tool that allows you to enter data directly into the table, then you may also find that you can also bulk copy and paste data directly into that table (SSMS supports this). So you could copy all the data from a spreadsheet, then paste it directly into the database table.
SQL Server Management Studio (SSMS) allows you to paste multiple rows of data into a table. -
Import the data
Some tools enable you to import a file that contains all the data required. For example, you could import an Excel spreadsheet or a .csv file.
The BCP command line utility is a popular tool for doing bulk copy operations into SQL Server. SQL Server Management Studio has an import wizard that assists you through the process.
An "Import Flat File" wizard was introduced in SSMS 17.3. This simplifies the process of importing flat files such as .csv and .txt. -
Website or other custom application
These days, most databases are built as part of the backend of an application such as a website, CRM, financial application, etc. Such applications usually enable the end user to insert and update data. Typically, the user will insert data via a form. Behind the scense, the form will insert the data into the correct tables in the database. In this case, the user doesn't even need to know what tables are in the database. All they see is the form. One form could insert data into several tables.
