SQL Server 2014 - Adding Data

How to insert data into a SQL Server database.

In the previous lesson, we created a table in our database. We now have a database, and a table... but no data.

There are many ways of getting data into your database. Here are the main ones that come to mind:

Manually: Type data directly into your table rows.
Copy/Paste: Similar to the previous option, but this one is where you copy data from another source, then paste it into a table in your database.
Import: You can use the Import and Export Wizard to import data from another source.
SQL Scripts: You can run a SQL script that contains all data to insert.
Application/Website: Users update the database via an application or website.

Here's more detail on each of those methods.

Manually

We can use the Edit Top 200 Rows option to manually type data directly into the table rows.

Manually entering data is OK if you only have a little bit of data to enter. But it's a bit clunky and can impractical if you have a lot of data. Plus it doesn't really suit most business needs, where non-technical users need to be able to update the database.

In any case, here's how to manually enter data directly into the table:

In the Object Explorer, right click on the table you wish to open, and select Edit Top 200 Rows:
You can now start entering the data directly into your table.

Note that while using the table we just created, we don't need to enter data into the TaskId and DateCreated columns. This is because the they will be populated automatically (remember, we set TaskId to Is Identity and DateCreated to (GetDate())). No need to enter anything for StatusId yet, as we haven't created the Status table, plus our table design allows that field to be blank (we selected Allow Nulls).

Copy/Paste

You could use a similar method to the above by copying from another datasource and pasting into your database table. Of course, this will require that the source table has the same columns as the destination table. Similar to the manual method above, this is OK for a small number of records but not for a lot of records.

Here's how to copy/paste into your table:

Select all required records from the datasource
In the destination database (i.e. the one you want to populate with data), right-click on the destination table and select Edit Top 200 Rows
Select an empty row by right-clicking in the left-most column (it's more of a button to the left of your left-most column that allows you to select the whole row) and select Paste from the contextual menu:

If you need to paste more than 200 rows, click the Show SQL Pane icon from the toolbar to display the SQL statement behind the 200 rows being displayed. Simply change the 200 to a larger number of rows as required.

Screenshot of pasting data into a table in SSMS

Note that this will work up to a certain extent, but you may encounter times where have so much data to transfer that you need to use another method.

Import

You can import data from another datasource. The end result is similar to the copy/paste method (i.e. data is copied across to the destination database), but importing the data is more flexible and could be more suitable on many occasions. For example, you can select data from multiple views and tables, and you can write a query on the fly to import only the data you need.

To import data, right-click on the database and select Tasks > Import Data... and follow the Wizard from there.

Screenshot of selecting the Import Data option

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. These include:

SQL Server
Flat files
Microsoft Office Access
Microsoft Office Excel

You can also start the SQL Server Import and Export Wizard from the Windows Start menu, from within SQL Server Data Tools (SSDT), and via the command prompt (by running DTSWizard.exe which you may find in either C:\Program Files\Microsoft SQL Server\100\DTS\Binn or in C:\Program Files\Microsoft SQL Server\120\DTS\Binn or other location depending on your configuration and drive letter).

SQL Scripts

In many cases, you will find it more efficient to run a SQL script that contains the data you need to insert. You can use the SQL INSERT statement to insert just the data you specify in the statement.

SQL scripts are great for inserting static/reference data (like say, countries/regions). They can be stored and run again any time it's needed (for example on another database). Scripts are not usually so good for data that constantly changes (like customer details). You probably wouldn't be keeping a copy of outdated data in a SQL script. But there are always exceptions. For example, you could use such a script to populate a customer table in your testing/development environment.

More about SQL scripts coming up.

Application/Website

Most SQL Server databases are the backend data storage for a front-end application. Users of the application are responsible for adding data to the database (as well as editing it). Therefore, many of the tables in your database will be updated via the application. In this scenario, the application is updating the database using SQL scripts.

The difference between these scripts and the ones we discussed above is that, the scripts being used in the application will be dynamic. They will accept data as a parameter that is passed to the script. So the user can enter say, an email address into the application, and unbeknownst to him, the application runs a SQL script that takes his email address, validates it, adds it to the script, and if it passes all the business/validation rules, inserts it into the database.

These SQL scripts can be placed directly into your website code (PHP, ColdFusion etc), or they can be stored in the database as Stored Procedures or Views and run only when the application says so.

Seeing as SQL scripts are such an integral part of SQL Server (and most other database management systems), next we'll look more closely at SQL scripts.