The INSERT reservered word is part of the Data Manipulation Language (DML) defined by Codd in 1970 and is used to add data to a table in the database. Today, I would like to review the different ways to insert data using the AdventureWorks Sample database supplied by Microsoft.
1 – Many times a table is defined with a surrogate key, an autoincrement column. You can specify values for this column by setting the INDENTITY INSERT property of the table. The following code adds a row to the Human Resources Department table with a key value of 17.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<span style="color: #008000;">-- MS sample database USE [AdventureWorks2008R2]; GO -- Specify the identity column. Yes! SET IDENTITY_INSERT HumanResources.Department ON; GO -- Insert data into table, choose the next identity value INSERT INTO HumanResources.Department (DepartmentID, Name, GroupName, ModifiedDate) VALUES (17, 'Mechanization', 'Manufacturing', GETDATE()); GO -- Specify the identity column. No! SET IDENTITY_INSERT HumanResources.Department OFF; GO </span> |
2 – The next example adds a row to the sample table but allows the system to pick the next key value.
|
1 2 3 4 |
<span style="color: #008000;">-- Insert data into table, let table determine next identity value INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate) VALUES ('Production Analytics', 'Manufacturing', GETDATE()); GO </span> |
3 – Sometimes, there is a need to insert several rows at a time. Before SQL Server 2008, one would use the UNION clause to join recordsets together. The example below demonstrates this technique.
|
1 2 3 4 5 6 |
<span style="color: #008000;">-- Inserting multiple records, SQL 2005 or below syntax INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate) SELECT 'Department A', 'Manufacturing', GETDATE() UNION ALL SELECT 'Department B', 'Manufacturing', GETDATE() UNION ALL SELECT 'Department C', 'Manufacturing', GETDATE() ; GO </span> |
4 – With the release of SQL Server 2008, developers can now use the recordset syntax to specify several rows of data to insert at one time. The example below shows this technique.
|
1 2 3 4 5 6 7 |
<span style="color: #008000;">-- Inserting multiple records, SQL 2008 syntax INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate) VALUES ('Department D', 'Manufacturing', GETDATE()), ('Department E', 'Manufacturing', GETDATE()), ('Department F', 'Manufacturing', GETDATE()); GO </span> |
5 – A very powerful but under used syntax allows the developer to insert into a table given the results of a stored proceedure. The example below exhibits this technique.
|
1 2 3 4 5 6 7 |
<span style="color: #008000;">-- Insert via stored proceedure call CREATE PROCEDURE usp_Return_One_Row AS SELECT 'Department G', 'Manufacturing', GETDATE(); GO INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate) EXEC usp_Return_One_Row; GO </span> |
6 – Last but not least, the SELECT INTO variation allows a developer to create a table on the fly. This is very useful when you have a table with a larger number of rows and you want to end up with a smaller subset. Since this statement can be minimally logged, it uses less log space than performing a delete against the orginal table.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000;">-- Add a new schema for the table CREATE SCHEMA STAGE AUTHORIZATION dbo; GO -- Create a new table by insertion SELECT * INTO STAGE.Department FROM HumanResources.Department; GO -- Show the data SELECT * FROM HumanResources.Department; GO </span> |
In summary, working with databases is vital in the information age. That said, adding data is a daily task that developers and administrators perform. Knowing the different variantions of the INSERT statement will help you select the correct syntax to use.
Oh my goodness! an incredible article dude.
Very great information can be found on web blog.