How to use the SQL INSERT INTO command

This article will teach you the basics of using SQL Server's INSERT INTO command. Once you've got the syntax down it is really quite easy!

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• SQL Server Management Studio

1. SQL Insert Into can be used in two different ways. One method is used if the data you are manually defining the data you are inserting. The second involves programmatically selecting the data via a query.

2. Let's show an example of manually defining the data:
   CREATE TABLE #HiringData
   (Person varchar(50),
   HireDate datetime,
   Position varchar(50))
   
   INSERT INTO #HiringData
   (Person,
   HireDate,
   Position)
   VALUES ('John Smith','May 14, 2009','Department Manager')
   
   SELECT * FROM #HiringData
   
   DROP TABLE #HiringData

3. The first thing we've done is to create a temporary table to insert data into using the CREATE TABLE command. We then defined the fields that should be in the temporary table. The # sign preceding the table indicates it is a temporary table.

4. The next step is to use the INSERT INTO command along with the table name to insert data into the table. After that, we define the fields that we want to insert into by naming them separated with a comma. Defining the fields is optional. You could just write:
   
   INSERT INTO #HiringData
   VALUES ('John Smith','May 14, 2009','Department Manager')
   
   However defining the fields can be a good idea as it ensures the data is always going where you want it. In addition if you ever need to insert data into only some of the columns in a table then you must define which fields you are inserting data into.

5. Next, using the VALUES command we can insert the data into the table. All non-numerical data should be surrounded by single quote marks including dates.

6. Next we have a simple SELECT * FROM #HiringData to select the data from the table to confirm that it is there.

7. Since this is only a test example and we've put the data into a temporary table we then use the DROP TABLE command to drop the table and remove it from the database. This method of inserting data works well if there is only one record to insert.

8. If you have multiple records to insert, you may want to use this syntax instead as it only requires writing one INSERT INTO statement.
   INSERT INTO #HiringData
   (Person,
   HireDate,
   Position)
   SELECT 'John Smith','May 14, 2009','Department Manager'
   UNION ALL
   SELECT 'Mary Jones','June 7, 2009','Marketing Manager'
   UNION ALL
   SELECT 'Tim Thomas','July 1, 2009','Field Technician'

9. Notice that in this method you do not use the VALUES command. Instead you "select" the data you want and then use the UNION ALL command to union that data together.

10. Finally let's show an example where the data is selected from a query:
    INSERT INTO #HiringData
    (Person,
    HireDate,
    Position)
    SELECT PersonName,StartDate,Title
    FROM CompanyEmployees
    WHERE StartDate Between '2009-05-01' and '2009-08-01'

11. In this example, we are selecting data from another table called company employees.
    - Make sure that you are inserting compatible data types. Trying to insert characters into a integer field doesn't work.
    - Working with dates can be tricky especially if one table is storing the date in a non-datetime field and the other is using a datetime field.
    - Always make sure you are selecting the same number of columns as you have said you want to insert.

12. One last example:
    SELECT PersonName,StartDate,Title
    INTO #HiringData
    FROM CompanyEmployees
    WHERE StartDate Between '2009-05-01' and '2009-08-01'

13. In this case you are using a SELECT INTO which behaves the same as an INSERT INTO.
    - You can only use a SELECT INTO if you want to create the table at the same time as you want to insert the data into it. For example in STEP 2 we first had to create the table and then insert data into it.
    - This should be used carefully as SQL Server Management Studio will automatically define your field types for you. In most cases it will keep the data type of the source data so if that isn't what you want then it is best to define the table yourself before hand. This method is quick though and can be useful if used with wisdom.