SQL Insert statement is used to insert a new records in table.
SYNTAX
INSERT INTO table_name (col1, col2, col3,...) VALUES(val1, val2, val3,.....)
Lets look at an example of Insert statement in SQL.
First we will Create a new table then Insert records in a table. Lets create a new table in SQL named as Demo.
CREATE TABLE dbo.Demo
(
DemoId INT PRIMARY KEY,
DemoCode VARCHAR(50),
RegDate DATE
)
As you can see a below screenshot, a new table Demo is created in SQL Server database. Currenly table does not have any data.
SELECT * FROM dbo.Demo
Lets Insert records in table using INSERT command.
When inserting a records in a table, all text values are enclosed within a single quotes ‘ ‘ย while for an integer, numeric, or decimal values no need to provide any qoutes.
INSERT INTO dbo.Demo (DemoId, DemoCode, RegDate) VALUES (1,'XV77V','2015-08-01')
As you can see query completed successfully and showing 1 row affected, Lets check whether the record is inserted into table or not. Lets use Select Statement to fetch the records from table.
SELECT * FROM dbo.Demo
As you can, a record is inserted into a table successfully.
Insert records when table having an identity column
In case when table have an identity column, then you do no need to provide a value for an identity column because value for an identity column is generated by SQL Server automatically.
Lets create a new table named as DEMO1 having an identity on column DemoID.
CREATE TABLE dbo.Demo1 ( DemoId INT IDENTITY(1,1), DemoCode VARCHAR(50), RegDate DATE )
Now, we will insert records in table Demo1, remember we will not provide a value for DemoId column as it is an identity column.
INSERT INTO dbo.Demo1
(DemoCode, RegDate)
VALUES
('XV77V','2015-08-01')
Lets check the table, as you see you have not provided a value for DemoId column during insert even then a value for this column is generated by SQL Server.
Therefore you do not need to provide a value for identity column during insert.
SELECT * FROM dbo.Demo
Insert values for specific columns in table
In case if you do not want to insert values for all columns then you need to provide only those columns names and their values in insert statement.
Suppose, if you want to insert values for only DemoCode then you need to provide a DemoCode column and its value only in insert statement.
Rememeber, null value/default values is inserted to those columns whose value is not provided during insert except identity column as value for identity column is provided by SQL server automatically.
Following statement, inserts records in a tableย Demo1 only for DemoCode column.
INSERT INTO dbo.Demo1
(DemoCode)
VALUES
('XDE90')
Lets check the table, to ensure that record is inserted.
SELECT * FROM dbo.Demo1
As you can see , a null value are assigned to RegDate column as we have not provided a value for this column during insert while value for identity column is generated by SQL Server Automatically.
Insert Multiple records in table
You can also insert multiple records in table at a single time using INSERT Statement.
Lets insert three rows in table Demo1 using Insert statement.
INSERT INTO dbo.Demo1
(DemoCode, RegDate)
VALUES
('XV77E','2015-09-02'),
('XV76T','2015-09-01'),
('XV76F','2015-09-03')
Lets check the table demo1.
1,304 total views, 1 views today