Inserting Records in a SQL Database Table (Done)
In SQL, data is inserted into a table using the INSERT statement. The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted.
For this exercise on Modifying data with an INSERT statement, we are going to continue with the AdventureWorks Sample Database which you should already have on your computer. If not, follow this link on how to download the AdventureWorks Lightweight (LT) sample database.
1 Open the SQL Server Management Studio (SSMS) on your computer. If you don't have it installed and setup, follow the link.
2 Once you open SSMS, Right-click on the AdventureWorksLT2022 database. Then select New Query. This should open the query window.
3 Copy and Paste the table create script below into the Query window.
4 Click "Execute" to create the EntryLog table in the AdventureWorksLT2022 database.
4 The EntryLog table is now create in the database with the message: Commands completed successfully.
How to Insert records into a table using the INSERT statement
Now that we have created the EntryLog table in the above example, we are ready to insert data into the table by using the INSERT statement.
First, open the a New Query window and enter the following code to query the SalesLT.EntryLog we just created. Then click "Execute".
The query results will show the columns in the new table but no rows, because the table is empty. At this point, you are ready to enter some data into the empty table.
Open the SQLQuery window and copy and paste the INSERT statement, to insert a new row into the SalesLT.EntryLog table.
1. Execute the following statement to insert a row into the SalesLT.EntryLog table that was created in the previous task. This is the basic syntax.
-- Standard syntax
INSERT INTO SalesLT.EntryLog
(EntryTime, SalesPerson, CustomerID, Phone, Email, Description)
VALUES
('2023-06-01T12:30:00', 'Mark Miller', 100, '301-344-5211', '[email protected]', 'Peter Piper picked a peck of pickled peppers. A peck of pickled peppers Peter Piper picked.');
GO
Note: The two hyphens -- in front of a line indicates that the line is a comment and the text will be ignored by the compiler.
2. The following statement shows how you can change the order in which the parameters are provided by switching the placement of the ProductID and ProductName in both the field list (in parentheses) and in the values list.
-- Changing the order of the columns
INSERT INTO SalesLT.EntryLog
(SalesPerson, Phone, Email, CustomerID, EntryTime, Description)
VALUES
('Woody Harrison', '501-404-2781', '[email protected]', 112, '2023-06-02T12:00:00', 'How much wood would a woodchuck chuck if a woodchuck could chuck wood?');
GO
3. The following statement demonstrates that the names of the columns are optional, as long as the values are listed in the correct order. This syntax is not recommended when writing query scripts because it will be harder for others to understand your code.
-- Skipping the column list, but keeping the values in order
INSERT INTO SalesLT.EntryLog
VALUES
('2023-06-03T12:30:00', 'Tayor Swift', 114, '213-804-1187', '[email protected]', 'She sells seashells by the seashore, while I scream, you scream, we all scream for ice cream.');
GO
4. Since the Email and Description column allows NULL values and no value is being provided, the Email and Description column name and value can be dropped from the statement completely. The table also has a default value defined for the EntryTime field.
-- Dropping the Email and Description columns and opting for the EntryTime field - default value.
INSERT INTO SalesLT.EntryLog
([EntryTime], [SalesPerson], [CustomerID], [Phone])
VALUES
(DEFAULT, 'Jeff Gorvette', 115, '702-633-7187');
GO
5. Inserting the results of a SELECT query into the SalesLT.EntryLog table.
-- Insert the results of a SELECT query into a table.
INSERT INTO SalesLT.EntryLog (SalesPerson, CustomerID, Phone, Email, Description)
SELECT SalesPerson, CustomerID, Phone, EmailAddress, 'Mr Lucky is a Fuzzy Wuzzy bear. Fuzzy Wuzzy had no hair. Fuzzy Wuzzy wasn’t fuzzy, was he?'
FROM SalesLT.Customer
WHERE CompanyName = 'Metropolitan Sports Supply';
GO
This query shows that 2 rows were affected and the results pane below shows that two new rows were added to the table. Those two rows (EntryLogID 6 & 7) are the rows that were retrieved by the SELECT query.
Things you should know about Adding Rows with INSERT
The INSERT statement adds one or more new rows to a table. In a simplified treatment, INSERT has this form:
This statement causes the data_values to be inserted as one or more rows into the named table or view. Column_list is a comma-separated list of column names that can be used to specify the columns for which data is supplied. If column_list is not specified, all the columns in the table or view receive data.
When a column_list does not name all the columns in a table or view, a value of NULL (or the default value if a default is defined for the column) is inserted into any column not named in the list. All columns not specified in the column list must either allow null values or have a default assigned.
INSERT statements do not specify values for the following types of columns as values are generated for columns of these types:
Columns with an IDENTITY property that generates the values for the column.
Columns that have a default value.
Computed columns.
Note