Introducing the INSERT command in SQL Server

The insert statement is used to add data to a table in SQL Server

Syntax:

 INSERT INTO table_name (column_list) VALUES (value_list)

Explain:

• table_name: the name of the table you want to add new data
• column_list : list of columns, separated by ","
• value_list: is a list of values corresponding to the column, separated by ","
• Note: The value pairs of column and value correspond and match in order and quantity with each other. Need to follow the correct syntax

For example:

 INSERT INTO Person.PersonPhone (BusinessEntityID, PhoneNumber, PhoneNumberTypeID, ModifiedDate) VALUES (299,'699-511-0142,1','2020-10-12')

Short syntax (no need to define columns, just specify the correct order of values):

 INSERT INTO Person.PersonPhone VALUES (20777,'699-511-0143',1,'2020-10-12')

The values of the corresponding columns are as follows:

• BusinessEntityID: 299
• PhoneNumber: 699-511-0142
• PhoneNumberTypeID: 1
• ModifiedDate: 2020-10-12

Some situations should be noted with the INSERT . command

Test this insert statement in AdventureWorks2019:

 USE [AdventureWorks2019] GO INSERT INTO [Production].[Product] ([Name] ,[ProductNumber] ,[MakeFlag] ,[FinishedGoodsFlag] ,[Color] ,[SafetyStockLevel] ,[ReorderPoint] ,[StandardCost] ,[ListPrice] ,[Size] ,[SizeUnitMeasureCode] ,[WeightUnitMeasureCode] ,[Weight] ,[DaysToManufacture] ,[ProductLine] ,[Class] ,[Style] ,[ProductSubcategoryID] ,[ProductModelID] ,[SellStartDate] ,[SellEndDate] ,[DiscontinuedDate] ,[rowguid] ,[ModifiedDate]) VALUES ('Laptop Thinkpad L470' ,'SKU-001' ,0 ,0 ,null ,1000 ,750 ,0 ,0 ,null ,null ,null ,null ,0 ,null ,null ,null ,null ,null ,'2008-05-01' ,null ,null ,NEWID() ,'2022-05-01' ) GO

This command gives an error:

 USE [AdventureWorks2019] GO INSERT INTO [Production].[Product] ( [ProductID], [Name] ,[ProductNumber] ,[MakeFlag] ,[FinishedGoodsFlag] ,[Color] ,[SafetyStockLevel] ,[ReorderPoint] ,[StandardCost] ,[ListPrice] ,[Size] ,[SizeUnitMeasureCode] ,[WeightUnitMeasureCode] ,[Weight] ,[DaysToManufacture] ,[ProductLine] ,[Class] ,[Style] ,[ProductSubcategoryID] ,[ProductModelID] ,[SellStartDate] ,[SellEndDate] ,[DiscontinuedDate] ,[rowguid] ,[ModifiedDate]) VALUES ( 1001, 'Laptop Thinkpad L470' ,'SKU-001' ,0 ,0 ,null ,1000 ,750 ,0 ,0 ,null ,null ,null ,null ,0 ,null ,null ,null ,null ,null ,'2008-05-01' ,null ,null ,NEWID() ,'2022-05-01' ) GO

Reason: in Product table, column ProductID is primary key and is assigned self-incrementing attribute, in SQL Server is not assigned a value for column with self-incrementing value to ensure data integrity, so, we just don't insert data into the ProductID column and that's ok, that column will be automatically generated by SQL Server according to the self-incrementing mechanism.

INSERT get return information

All newly inserted data will be stored in the inserted variable, and to get the inserted data, we use the OUTPUT command. If you want to output more columns, separate the columns by commas ",":

For example, get the ID just inserted

 USE [AdventureWorks2019] GO INSERT INTO [Production].[Product] ([Name] ,[ProductNumber] ,[MakeFlag] ,[FinishedGoodsFlag] ,[Color] ,[SafetyStockLevel] ,[ReorderPoint] ,[StandardCost] ,[ListPrice] ,[Size] ,[SizeUnitMeasureCode] ,[WeightUnitMeasureCode] ,[Weight] ,[DaysToManufacture] ,[ProductLine] ,[Class] ,[Style] ,[ProductSubcategoryID] ,[ProductModelID] ,[SellStartDate] ,[SellEndDate] ,[DiscontinuedDate] ,[rowguid] ,[ModifiedDate]) OUTPUT inserted.ProductID, inserted.Name VALUES ('Dell Inspiron 5570' ,'SKU-002' ,0 ,0 ,null ,1000 ,750 ,0 ,0 ,null ,null ,null ,null ,0 ,null ,null ,null ,null ,null ,'2008-05-01' ,null ,null ,NEWID() ,'2022-05-01' ) GO