We will eventually want to add data to the tables in our database. This section will review the syntax of how to insert data.

Column Requirements

We have discussed the name and the data type of a column. A part of the table we have not yet discussed are the requirements of each column. Certain columns of the table are required—like a primary key; however, not every column must have data. Think of a form you recently filled out—did you leave part of it blank? We can specify which of our columns are required to have data and which are not required. This knowledge about the column will become important as we construct our insert statements.

 

Table 3 Condition Requirements

Condition Explanation
NOT NULL The column must be present in an insert statement
NULL The column is not required for an insert statement

 

In our Cat table, let’s take a second look at the table, but this time include the requirement conditions in the third column.

**Missing Image for Cat Table**

We can insert all the columns in the table in a single insert statement; however, the column is not required unless the column in the table has a NOT NULL condition. In our Cat table, the values we must insert include:

CatIdentifier – This is our primary key and requires a value; however, we don’t need to specify a value because this is automatically done for us by the system.  When we created the table, we used the IDENTITY keyword on this column.  The IDENTITY column then tells SQL Server to automatically enter a value for this column.  We discuss this further in the auto increment page.

ColorOfCat – this has a VARCHAR (variable length) data type.

KeptIndoors – This has a Bit (yes/no) data type.

NameOfCat – This column has a VARCHAR data type.

Just as the saying goes—“The more, the merrier” —we should look to include as much information as we can in our insert statement. But occasionally we might look to provide the absolute minimum, though we shouldn’t make it a habit.

The syntax to build an insert statement which includes ALL the columns looks like this–

INSERT INTO [TableName] [ColumnNames] VALUES [ListOfValues]

Our Cat table would look like the statement below. In this instance we are inserting all the columns except for the primary key, which the database will assign for us. You will notice the data we want to insert must be in the same order as the columns we specify.

INSERT INTO [Cat]

([BirthDateOfCat],[BreedOfCat],[ColorOfCat],[KeptIndoors],[MonthsWithOwner],[NameOfCat],[OwnerIdentifier])

VALUES (’01/01/2014′, ‘Himalayan’, ‘Gray’, 0, 18, ‘Shadow’, 84);

 

OUTPUT

(1 row(s) affected)

 

In this example, we are inserting all the columns, so we aren’t required to provide the column names. This statement and the above statement are the same.

 

 

INSERT INTO [Cat]

VALUES (’01/01/2014′, ‘Himalayan’, ‘Gray’, 0, 18, ‘Shadow’, 84);

OUTPUT

(1 row(s) affected)

Creating Incomplete Rows

When we do not want to insert all of the columns, we must specify the columns we are inserting. This is especially true if we are using an auto-incremented number, like our primary key. This example shows the insert for only the required columns.

INSERT INTO [Cat]

([ColorOfCat],[KeptIndoors],[NameOfCat])

VALUES

(‘White’, 0, ‘Dru’);

OUTPUT

(1 row(s) affected)

 

This example will create a cat record; however, we won’t have an owner and we won’t know what breed of cat we have. We will come back to this record when we talk about changing data.

Inserting Data From Another Table

We can insert data by specifying each value as we do with an insert statement; however, we can also copy or move data that already exists in a table to another table. We can combine our knowledge of an insert statement with a select statement to copy data. We could even copy just a subset or even a query where we do some calculation of data into another table.

Our syntax for inserting data by selecting data from another table looks like this:

INSERT INTO [TableName] ([NameOfColumn],[NameOfColumn]) SELECT [NameOfColumn],[NameOfColumn]) FROM [TableWithData]

We have a table name AnimalsToBePlaced that has both cats and dog in the table. We want to put this information into the appropriate table. This is a common scenario when data might be loaded into a temporary or staging table, and then we have to move the data to the appropriate data. Often we do some data manipulation before we insert the data. In this example, we only need to insert the data into the appropriate table. We will use the WHERE clause to accomplish this.

INSERT INTO Dog

( BirthDateOfDog ,BreedOfDog ,ColorOfDog ,KeptIndoors , MonthsWithOwner ,NameOfDog ,OwnerIdentifier)

SELECT BirthDateOfAnimal ,BreedOfAnimal ,ColorOfAnimal ,KeptIndoors ,MonthsWithOwner ,NameOfAnimal ,OwnerIdentifier

FROM AnimalsToBePlaced

WHERE TypeOfAnimal = ‘Dog’;

 

OUTPUT

(7 row(s) affected)

 

INSERT INTO Cat

( BirthDateOfCat ,BreedOfCat ,ColorOfCat,KeptIndoors , MonthsWithOwner ,NameOfCat ,OwnerIdentifier       )

SELECT BirthDateOfAnimal ,BreedOfAnimal ,ColorOfAnimal ,KeptIndoors ,MonthsWithOwner ,NameOfAnimal ,OwnerIdentifier

FROM AnimalsToBePlaced

WHERE TypeOfAnimal = ‘Cat’;

OUTPUT

(7 row(s) affected)

 

We will insert 7 records into both the dog and cat tables.

Creating Tables on the Fly

In the example above, the tables were already created for us and we simply populated them. We can also create a table ‘on the fly,’ or at runtime of the query, and the database will use the data returned to create the column names and data types. This should really only be used in testing or for temporary reporting needs. Primary Keys do not get created in this; however, data types get copied over and some RDBS implementations will copy the NULL requirement setting. We will go over how to create tables later **Add link**.

In this scenario our syntax changes a bit. We begin with the select syntax and then use the keyword INTO and the name of the table we want to create. When we complete this query a new table will be created for us.

SELECT [NameOfColumn],[NameOfColumn]) INTO [NewTableName] FROM [TableWithData]    (Don’t forget your semicolon)

In our example we will create a new table called DogsInEstablishedHomes. If you try to query from this nonexistent table now, you will receive an error.

 

SELECT * FROM DogsInEstablishedHomes;

 

OUTPUT

Invalid object name ‘DogsInEstablishedHomes’.

 

Now, let’s look at the query we will use to create this new table.

 

Select NameOfDog, BreedOfDog, ColorOfDog, MonthsWithOwner

INTO DogsInEstablishedHomes

FROM Dog

WHERE MonthsWithOwner > 24;

 

OUTPUT

(116 row(s) affected)

 

Once the query has been run and the table created, a query to pull data from the table will execute successfully and the results will be returned.

SELECT * FROM DogsInEstablishedHomes;

OUTPUT (Modified)

NameOfDog   BreedOfDog   ColorOfDog   MonthsWithOwner
———              ————          ———-           ————-

Odie                  Abruzzenhund        Yellow                     442
Pluto                 Bloodhound             Yellow Orange      300
Hercules           Carolina Dog            Black and White     25
Hercules           Carolina Dog            Black                        26
Hercules           Carolina Dog            White and Yellow   27
Clifford              Carolina Dog            Red                          28
Clifford              Bulldog                     Yellow                      29
Clifford              Carolina Dog            Brown                     30
Clifford              Carolina Dog            Tan                          50
Clifford              Carolina Dog            White                      49

(116 row(s) affected)