How to Add data into a SQL Server database. Lesson-5

How to Add 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:

1. In the Object Explorer, right click on the table you wish to open, and select 

2. You can now start entering the data directly into your table.

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:

1. Select all required records from the datasource

2.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

3.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:

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.

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).