How to use the database INSERT DML statement and examples

INSERT is a DML statement for entering data into a record. The INSERT DML statement is usually used when entering data in a database TABLE.

How to use the INSERT DML statement

How to INSERT without specifying table COLUMN.

Syntax code
Insert into [table Name]
values ([value1, value2, value3…]);

• Question: Enter data without specifying a column.

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForInsert;
CREATE TABLE UserInfoForInsert (
    UserId int,
    UserNm varchar(255),
    UserAge  int , 
    UserLocation varchar(500)
); 

-- 1. How to INSERT without specifying table COLUMN
insert into  UserInfoForInsert values ('202401','john',37,'USA');
-- INSERT   
select * from UserInfoForInsert;

INSERT DML statement method for multiple rows simultaneously by specifying COLUMN.

syntax code.
Insert into [table name] ([column1, column2, column3…])
values ([value1, value2, value3…]);

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForInsert;
CREATE TABLE UserInfoForInsert (
    UserId int,
    UserNm varchar(255),
    UserAge  int , 
    UserLocation varchar(500)
);  
-- 2. How to INSERT multiple rows at the same time by specifying COLUMN.
insert into  UserInfoForInsert(UserId,UserNm,UserAge) 
values
('202401','Obama',60),
('202402','Biden',84),
('202403','Bush',90),
('202404','Hill',60),
('202405','Lee',43)
-- INSERT   
select * from UserInfoForInsert;

How to immediately INSERT the SELECT result.

Syntax code.
Insert into [table name] ([column1, column2, column3…])
SELECT [column1, column2, column3…]
FROM [table name];

• Question: Please put the selected data back into the target table.
  However, insert the data in the last column arbitrarily.

-- 3. How to immediately INSERT the SELECT result.
insert into  UserInfoForInsert(UserId,UserNm,UserAge,UserLocation) 
select UserId,UserNm,UserAge , 'white house'   
from UserInfoForInsert;
-- INSERT   
select * 
from UserInfoForInsert;

How to use OUTPUT in INSERT statement.

After inserting a row into a table, you can use the OUTPUT clause to return the results through a table variable.

• Question: Output using table variables and OUTPUT so that you can immediately check the INSERT data.

-- 4. How to use OUTPUT in INSERT statement.
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForInsertOutput;
CREATE TABLE UserInfoForInsertOutput (
    UserId int,
    UserNm varchar(255),
    UserAge  int 
); 
 
 DECLARE @outPutTable table(UserId int,
        UserNm varchar(255),
        UserAge  int );  
INSERT UserInfoForInsertOutput 
    OUTPUT INSERTED.UserId, INSERTED.UserNm, INSERTED.UserAge  
        INTO @outPutTable 
VALUES ('202405','Lee',43);  
select * from  @outPutTable;
-- Check if the table has been entered
-- select * from UserInfoForInsertOutput;

How to COPY all data in a table.

syntax.
select * into [NEW Table name]
from [Table name];

• Question: Enter all data into the new table using the select statement.

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForInsertCopy;
select * into UserInfoForInsertCopy 
from UserInfoForInsert;

-- Check the copied table.
select *
 from UserInfoForInsertCopy;

How to use INSERT DML statement using SSMS

SSMS usage path.

• Object Explorer - Select table name - Right mouse - Select Edit Top 200 Rows.
• When you enter the data to be inserted and press Enter, the data will be reflected immediately.
• SSMS capture screen.