{"id":94832,"date":"2022-10-04T18:35:08","date_gmt":"2022-10-04T18:35:08","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94832"},"modified":"2022-10-04T18:35:08","modified_gmt":"2022-10-04T18:35:08","slug":"the-basics-of-inserting-data-into-a-sql-server-table","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/the-basics-of-inserting-data-into-a-sql-server-table\/","title":{"rendered":"The Basics of Inserting Data into a SQL Server Table"},"content":{"rendered":"

This article is part of Greg Larsen's continuing series on Learning T-SQL. To see all the items in the series, click here<\/a>.<\/strong><\/p>\n\n

Before data can be read from of a SQL Server database table, the table needs to contain rows of data. One of the most typical ways to get data into a table is to use the INSERT<\/code> statement. One row or multiple rows can be inserted with a single execution of an INSERT<\/code> statement. You can even use the output of a stored procedure to insert rows. In this article, I will explore the basics of inserting data into a SQL Server table using the INSERT<\/code> statement.<\/p>\n

Syntax of the basic INSERT statement<\/h2>\n

As with most SQL Server commands, there are multiple ways to use them. The INSERT<\/code> statement is no different. For this article, let\u2019s review the syntax of the basic INSERT<\/code> statement found in Figure 1.<\/p>\n

INSERT [INTO] <object >[ (<column_list>)] \r\n  VALUES (<value_list1>)[,(<value_list2>)\u2026,(<value_list N>)];<\/pre>\n
Figure 1: Basic Insert statement<\/strong><\/p>\n
Figure 1 only contains the syntax for the basic INSERT<\/code> statement. For the complete syntax of the INSERT<\/code> statement, refer to the Microsoft Documentation<\/a>. You will see that there is quite a bit more syntax available, but in this article, I will include only the basics.<\/p>\n
The object <\/em>is the name of the table or view in which a single row or multiple rows will be inserted. The column_list <\/em>contains the comma delimited names of the columns that will be populated with values identified in the VALUES<\/code> parameter. The column_list <\/em>is only required if a subset of the object\u2019s columns is populated with values, although it can also be included if all the columns in the table are being populated.<\/p>\n
value_list1 <\/em>will include the values used to populate the columns inserted in the first row. If multiple rows are inserted with a single INSERT<\/code> statement, then value_list2<\/em> through value_listN <\/em>will be used to identify the additional sets of values for any other rows to be inserted.<\/p>\n
A few examples are provided to better understand how to use this basic syntax to insert data into a SQL Server table.<\/p>\n
Creating table to insert rows of data<\/h2>\n
Before the examples in this article can be executed, a target table for insert rows needs to be created. The code in Listing 1 will create that table.<\/p>\n
USE tempdb;\r\nCREATE TABLE dbo.Cars \r\n(\r\n   CarID tinyint NULL, \r\n   Manufacture varchar(30) NULL, \r\n   Model Varchar(30) NULL,\r\n   ModelYear int NULL, \r\n   PurchaseDate date NULL\r\n);<\/pre>\n
Listing 1: Code to create target table<\/strong><\/p>\n
The code in Listing 1 creates a table named dbo.Cars<\/code>,<\/em> that will be created in the tempdb <\/em>database. If you want to follow along and run the example code in this article, then run the code in Listing 1 to create the dbo.Cars<\/code> table on your SQL Server instance. Any database, even tempdb<\/code> as included in the code, will suffice.<\/p>\n
Populating each column in a table<\/h2>\n
An INSERT<\/code> statement can be used to insert a value for every column in a table, or a subset of columns. In this section, two examples will be shown that populate values for all the columns in the dbo.Cars<\/code> table. The code for the first example can be found in Listing 2.<\/p>\n
INSERT INTO dbo.Cars (CarID, \r\n           Manufacture,\r\n           Model, \r\n           ModelYear, \r\n           PurchaseDate)\r\nVALUES (1, 'Ford', 'F250', 2017, '2021-11-25');<\/pre>\n
Listing 2: Populating each column using the column list parameter<\/strong><\/p>\n
The code in Listing 2 inserts a single row into the dbo.Cars<\/code> table. This INSERT<\/code> statement uses the optional column_list <\/em>parameter, which in this case identifies every column in the table. Since every column in the dbo.Cars<\/em> table is being populated, the column list is not required. But including the column_list <\/em>parameter is a common practice for clarity and reducing breaking changes as it allows new columns with DEFAULT<\/code> constraints to be added without affecting existing code.<\/p>\n
The VALUES<\/code> clause in Listing 2 contains only the value_list1 <\/em>parameter since only one row is being inserted. The first value in column_list1 <\/em>specifies the numeric value 1. This value will be used to populate the first column identified in the column_list, <\/em>which in this case is CarID<\/code>. The second column in the column list Manufacture<\/code> is populated with the second value in column_list1<\/em> parameter, \u201cFord\u201d. Each of the following values in value_list1 <\/em>are used to populate each additional column listed in the column_list<\/em> parameter.<\/p>\n
The code in Listing 3 shows adding a second row to the dbo.Cars<\/code> table, where every column is populated with a value. But this example doesn\u2019t include the column_list <\/em>parameter.<\/p>\n
INSERT INTO dbo.Cars \r\nVALUES (2, 'Subaru', 'Outback', 2019, '2018-12-31');<\/pre>\n
Listing 3: Populating each column without using the column_list<\/em> parameter<\/strong><\/p>\n
When no column_list<\/em> parameter is specified, the values in the column list must be specified in a specific order to ensure the correct column gets populated with the correct value. The order to specify the values is based on the column\u2019s ordinal position in the table. The first value specified places a value in the first column in the table, the second value goes in the second column, and so on.<\/p>\n
Populating only a few columns with an INSERT statement<\/h2>\n
There are times when a row needs to be inserted, but there are no values available for every column in the table. For instance, suppose the dbo.Cars<\/code> table tracks the inventory of cars on a car dealer\u2019s parking lot. Typically, cars on a car lot have not yet been purchased. Because of this, the purchase date for a car will not be known until the car is bought by a customer. You can create a new row in the dbo.Cars<\/code> table without including a value for the PurchaseDate<\/code> column, the INSERT<\/code> statement in Listing 4 can be run. The value will either be NULL<\/code> or use a DEFAULT<\/code> constraint value if one exists.<\/p>\n
INSERT INTO dbo.Cars (Manufacture, Model, ModelYear, CarID)\r\nVALUES ('Chevrolet', 'Suburban', 2005,3);<\/pre>\n
Listing 4: Inserting a row without populating the PurchaseDate <\/em>column.<\/strong><\/p>\n
In Listing 4, a new Chevrolet Suburban was added to the dbo.Cars<\/code> table, without a purchase date. The code in Listing 4 didn\u2019t have the PurchaseDate<\/code> column listing in the column list parameter or a matching purchase data value in the VALUES<\/code> parameter. Therefore, this INSERT<\/code> statement populated all columns but the PurchaseDate<\/code> column.<\/p>\n
One other thing worth mentioning about Listing 4 is the first column listed in the column_list <\/em>and first value listed value_list1<\/em> parameters are not for populating the first column, ordinal position-wise in the dbo.Cars<\/code> table. The first ordinal column in the dbo.Cars<\/code> table is CarID<\/code>, <\/em>which is specified last in the column_list <\/em>of this example. <\/em>Therefore, this example demonstrates that the columns listed in the column_list <\/em>parameter and value list don\u2019t have to be specified in the ordinal position order relative to the other column\/value pairs listed. If you\u2019d like to know the ordinal positions of each column in the dbo.Cars<\/code> table, then you can run the code in Listing 5.<\/p>\n
SELECT column_id, name\r\nFROM  sys.columns\r\nWHERE OBJECT_ID = OBJECT_ID('dbo.Cars')\r\nORDER BY column_id;<\/pre>\n
Listing 5: Showing the original position for columns<\/strong><\/p>\n
To verify that each column was populated with the correct value and the PurchaseDate <\/em>column didn\u2019t get a value the code in Listing 6 can be run.<\/p>\n
SELECT * FROM dbo.Cars where CarID = 3;<\/pre>\n
Listing 6: Reviewing the row inserted by the code in Listing 4<\/strong><\/p>\n
In Report 1 is the output when the code in Listing 6 is executed.<\/p>\n
<\/p>\n
Report 1: Output produced when Listing 5 is executed <\/strong><\/p>\n
Report 1 shows the PurchaseDate<\/code> column value was not populated with a value. Plus, all the other columns were correctly populated even though the columns in the column_list <\/em>parameter were specified in a different order then their ordinal position in the dbo.Cars<\/code> table.<\/p>\n
Automatically generating an identity column value when inserting rows<\/h2>\n
A table definition might have at most one column identified as an identity column. When a column has been defined as an identity column, the values assigned for the column will typically be automatically generated when each new row is inserted. The values generated are based on the seed <\/em>and increment <\/em>value associated with the identity column. The seed <\/em>value identifies the value of the identity column for the first row populated. Whereas the increment <\/em>value identifies the value that will be added to the previous inserted identity value to obtain the next identity value.<\/p>\n
When an identity column is defined on a table, the value for that column can\u2019t be manually populated with an insert statement. In order to demonstrate this, the dbo.Cars <\/code>table will need to be dropped and recreated by running the code in Listing 7.<\/p>\n
USE tempdb;\r\nGO\r\nDROP TABLE dbo.Cars;\r\nGO\r\nCREATE TABLE dbo.Cars \r\n(\r\n   CarID tinyint identity, \r\n   Manufacture varchar(30), \r\n   Model Varchar(30),\r\n   ModelYear int, \r\n   PurchaseDate date\r\n);<\/pre>\n
Listing 7: Dropping and recreating <\/strong>dbo.Cars<\/code> table<\/strong><\/p>\n
In Listing 7, the new dbo.Cars<\/code> table created specified that the CarID<\/code> column is an identity column without specifying an seed<\/em> and increment<\/em> value. When the seed <\/em>and increment <\/em>values are not identified, the default values of 1 will be used for the seed <\/em>and increment <\/em>values. The seed value is the first value used, and the increment is how much will be added to the previous value for the next value.<\/p>\n
If an INSERT<\/code> statement tries to manually insert an identity column value, an error will occur. To demonstrate this, the code in Listing 2 can be executed again. This second execution produces the error shown in Report 2.<\/p>\n
<\/p>\n
Report 2: Error received when inserting row<\/strong><\/p>\n
This error occurs because the code in Listing 2 tried to insert the value \u201c1\u201d into the identity column while the IDENTITY INSERT<\/code> property for the dbo.Cars<\/code> table is set to off. Before discussing this property first let\u2019s show the correct way to automatically insert rows when a table contains an identity column.<\/p>\n
The code in Listing 8 demonstrates how to automatically generate identity column values when using the basic INSERT<\/code> statement without providing the value for the CarID<\/code> column.<\/p>\n
INSERT INTO dbo.Cars (Manufacture, Model, ModelYear, PurchaseDate)\r\nVALUES ('Ford', 'F250', 2017, '2021-11-25');\r\n\r\nINSERT INTO dbo.Cars \r\nVALUES ('Subaru', 'Outback', 2019, '2018-12-31');\r\n\r\nSELECT * FROM dbo.Cars; <\/pre>\n
Listing 8: Automatically generating identity values when inserting a row<\/strong><\/p>\n
Report 3 shows the output from SELECT<\/code> statement when Listing 8 is run.<\/p>\n
<\/p>\n
Report 3: Output when Listing 8 is run<\/strong><\/p>\n
The code in Listing 8 has the INSERT<\/code> statements that were included in Listing 2 and 3, but with one exception. It doesn\u2019t contain the CarID <\/code>column in the column list parameter. Report 3 shows how the identity value on the first row inserted was assigned the value 1 and the next row inserted got an identity value of 2.<\/p>\n
IDENTITY_INSERT property and manually assigning identity values<\/h2>\n
Identity value can be assigned manually if needed. It is generally not a good practice to manually insert identity values. But there are times when you might need to assign an identify column manually, like if you need to recreate some missing data (for example an errantly deleted row).<\/p>\n
To do this, the IDENTITY_INSERT<\/code> option needs to be turned on for the table in which identity columns are going to be inserted. To demonstrate this, execute the code in Listing 9.<\/p>\n
SET IDENTITY_INSERT dbo.Cars ON;\r\n\r\nINSERT INTO dbo.Cars (CarID,Manufacture, Model, ModelYear)\r\nVALUES (10,'Chevrolet', 'Suburban', 2005);\r\n\r\nSET IDENTITY_INSERT dbo.Cars OFF;\r\nSELECT * FROM dbo.Cars;<\/pre>\n
Listing 9: Manually inserting an identity value.<\/strong><\/p>\n
This code first turned on the IDENTITY_INSERT<\/code> property on dbo.Cars<\/code> table using a SET <\/code>command. Turning on IDENTITY_INSERT<\/code> tells the SQL Server database engine that an INSERT statement will supply the identity value in an INSERT<\/code> statement. The INSERT<\/code> statement in Listing 9, inserts a new dbo.Cars<\/code> row, and supplies value of 10 for the CarID<\/code> column. Lastly, the code uses another SET<\/code> command to turn off the IDENTITY_INSERT<\/code> property.<\/p>\n
Note: The best practice is to turn on IDENTITY_INSERT<\/code> just before inserting identity values and turning it off immediately following the last INSERT<\/code> statement that assigns an identity value.<\/p>\n
Report 4 shows the output produced when the SELECT<\/code> statement is Listing 9 was executed. See how the third row has a value of 10 for the CarID<\/code> column.<\/p>\n
<\/p>\n
Report 4: Output when Listing 9 was executed <\/strong><\/p>\n
Care should be taken when manually setting identity values because it is possible to insert a row with an identity value that already exists.<\/p>\n
When identity values are assigned manually, SQL Server keeps track of the highest identity value added. It then uses this highest value to generate the next identity value when a new row is inserted (this is demonstrated in the next section).<\/p>\n
Add multiple rows to a table using a single INSERT statement<\/h2>\n
Prior to SQL Server 2008, only one row could be added to a table using the VALUES<\/code> clause of an INSERT<\/code> statement. With SQL Server 2008 or above, multiple rows can be inserted using a single INSERT<\/code> statement when it contains multiple items in the VALUES<\/code> clause. The code in Listing 10 shows how to insert 2 new rows into the dbo.Cars<\/code> table with a single INSERT<\/code> statement.<\/p>\n
INSERT INTO dbo.Cars (Manufacture, Model, ModelYear,PurchaseDate)\r\nVALUES ('Kia', 'Spectra', 2007,'2008-11-18'),\r\n    ('Nissan','King Cab',1983,'1998-05-14');\r\n\r\nSELECT * FROM dbo.Cars;<\/pre>\n
Listing 10: Inserting two rows with a single INSERT statement<\/strong><\/p>\n
By reviewing Listing 10, you can see there are two different sets of values listed in the VALUES<\/code> parameter. Each set of values is contained inside a set of parathesis, with a comma between them. When Listing 10 is executed, two rows will be inserted. The SELECT<\/code> statement in Listing 9 produced the output in Report 5.<\/p>\n
<\/p>\n
Report 5: Output when Listing 9 is run<\/strong><\/p>\n
The last two rows, with identity values 11 and 12, were the ones added with the single INSERT<\/code> statement.<\/p>\n
Using a SELECT statement to feed an INSERT statement<\/h2>\n
In all the examples so far, the values for each new row added were supplied by using the VALUES<\/code> parameter. But that is not the only way to provide values. A SELECT<\/code> statement can also provide new row values for an INSERT<\/code> statement, as is shown in Listing 11.<\/p>\n
INSERT INTO dbo.Cars(Manufacture, Model, ModelYear,PurchaseDate)\r\n  SELECT Manufacture, Model, ModelYear, SYSDATETIME()\r\n  FROM dbo.Cars \r\n  WHERE CarID = 1 OR CarID = 2;\r\n\r\nSELECT * FROM dbo.Cars;<\/pre>\n
Listing 11: Using a SELECT statement to identify values for new rows<\/strong><\/p>\n
In Listing 11, a SELECT statement was used to select the column values for two rows from the dbo.Cars<\/code> table that have CarID<\/code> values 1 and 2. <\/em>The two rows selected provided the values for the INSERT<\/code> statement. The last two rows added can be seen in Report 6.<\/p>\n
<\/p>\n
Report 6: Output when code in Listing 11 was executed<\/strong><\/p>\n
The rows with CarID<\/code> values of 13 and14 are the rows that were added. These rows have the same Manufacture<\/code>, <\/em>Model<\/code>, and <\/em>ModelYear<\/code> as the first two rows, except the PurchaseDate<\/code> is different. The PurchaseDate<\/code> values were set by using the SYSDATETIME()<\/code> function in the VALUES<\/code> clause.<\/p>\n
Inserting data from the output of a stored procedure<\/h2>\n
There are times when more complicated logic is needed to prepare rows for insert. For example, an application might need to run a stored procedure that reviews a table or a series of tables to determine what rows need to be inserted into a table.<\/p>\n
In this example code in Listing 12, a stored procedure is created that will determine what rows and values to insert. The stored procedure makes multiple passes through a WHILE<\/code> loop. Each pass through the loop programmatically determines if information will be inserted into a temporary table or not. Once the WHILE<\/code> loop completes, all the rows in the temporary table are displayed.<\/p>\n
Instead of outputting the rows to the screen, the output from the stored procedure will then be used to feed values into an INSERT<\/code> statement to create new rows in the dbo.Cars<\/code> table. The code for the described stored procedure can be found in Listing 12.<\/p>\n
CREATE PROC dbo.RecsToInsert\r\nAS\r\nDECLARE @I int = 0;\r\nCREATE TABLE #T (\r\nManufacture varchar(30), \r\nMadeUpModel Varchar(30),\r\nMadeupModelYear int, \r\nPurchaseYear date);\r\nWHILE @I < 2 BEGIN\r\n  SET @I = @I + 1;\r\n  INSERT INTO #T \r\n   SELECT CASE WHEN @I = 1 THEN 'Ford'\r\n         WHEN @I = 2 THEN 'Tesla' END AS Manufacture,\r\n       CAST (@I as CHAR(1)) AS MadeUpModel,\r\n       YEAR(SYSDATETIME()) AS MadeupModelYear,\r\n       SYSDATETIME() AS PurchaseDate;\r\nEND \r\nSELECT * FROM #T;\r\nDROP TABLE #T;<\/pre>\n
Listing 12: Sample Stored Procedure<\/strong><\/p>\n
The stored procedure created in Listing 11 will insert two rows into the temporary table #T<\/code>. Those two rows will then be displayed using a SELECT<\/code> statement. To execute this stored procedure and insert the two rows contained in the temporary table, execute the code in Listing 13.<\/p>\n
INSERT INTO dbo.Cars \r\nEXEC RecsToInsert;<\/pre>\n
Listing 13: Inserting the output of a stored procedure into a table.<\/strong><\/p>\n
The stored procedure dbo.RecsToInsert <\/code>was executed by specifying the \u201cEXEC<\/code>\u201d statement in conjunction with the INSERT<\/code> statement, similar to how the SELECT<\/code> statement was executed in Listing 11. The output produced by this stored procedure provides the values for the different rows and their values for the INSERT<\/code> statement. In this example, two rows output by the store procedure were inserted into the dbo.Cars<\/code> table.<\/p>\n
Note: While this method will not be available for other statements, it is very common to create a temporary table to receive the output of a stored procedure, then use that in an UPDATE<\/code>, DELETE<\/code>, MERGE<\/code>, etc. statement.<\/p>\n
Basic of Inserting Data into a SQL Server Table<\/h2>\n
If an application plans to capture data and store it in a SQL Server table, one of the primary ways to accomplish that task is with an INSERT<\/code> statement. INSERT<\/code> statements allow storing a single column value, multiple column values or all the column values into a table. It is also possible to insert multiple rows with a single INSERT<\/code> statement or a stored procedure. Knowing the basics of inserting data into a SQL Server table using the INSERT<\/code> statement is a key skill that every T-SQL programmer should have.<\/p>\n","protected":false},"excerpt":{"rendered":"
Before data can be read from of a SQL Server database table, the table needs to contain rows of data. One of the most typical ways to get data into a table is to use the INSERT statement. One row or multiple rows can be inserted with a single execution of an INSERT statement. You……<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525],"tags":[147174],"coauthors":[11330],"class_list":["post-94832","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","tag-t-sql-coding"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94832"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94832\/revisions"}],"predecessor-version":[{"id":94854,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94832\/revisions\/94854"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94832"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}