How to use insert into select statement in sql

How to use insert into select statement in sql.

INSERT INTO SELECT Statement: It is used to add new value(s) on existing table.

INSERT INTO SELECT Syntax:

Copy all column and add into existing table.

INSERT INTO new_table SELECT * FROM old_table WHERE column_name='value'....

Copy selected column and add into existing table.

INSERT INTO new_table (column1,column2,.....) SELECT column1,column2,... FROM old_table WHERE column_name='value'....

Description:

INSERT INTO: SQL Statement.
new_table: Name of table.
SELECT * FROM : SQL Statement
old_table: Name of table.
WHERE: SQL clause
column_name: Name of column
'value': User defined value







INSERT INTO SELECT Example:

Select * into ItemMobile from ItemDetails Where ItemCategory='Mobile' and ProductCountry='Korea' [Note: Creates new table]

Insert into ItemMobile Select * from ItemDetails Where ItemCategory='Mobile' and ProductCountry='United Stat'

Result:

ItemCode	ItemName	ManufactureCompany	ProductDate	ExpiredDate	ItemSerialNo	ItemCategory	ItemQuantity	ItemPrice	ProductCountry	ReceivedDate
4	iPhone	iPone	12/1/2024	12/1/2049	iP12345	Mobile	1000	500	United Stat	12/10/2025
5	Apple Mobile	Apple	12/11/2024	12/1/2049	A12345	Mobile	200	300	United Stat	12/10/2025
1	Samsung Mobile	Samsung	12/10/2024	12/1/2050	S12345	Mobile	500	240	Korea	12/10/2025
2	Samsung Pro	Samsung	12/10/2024	12/1/2050	SP1234	Mobile	100	300	Korea	12/10/2025

Select ItemCode,ItemName,ItemCategory,ItemPrice into ItemComputer from ItemDetails Where ItemCategory='Computer' and ProductCountry='Korea' [Note: Creates new table]



Insert into ItemComputer (ItemCode,ItemName,ItemCategory,ItemPrice) Select ItemCode,ItemName,ItemCategory,ItemPrice
from ItemDetails Where ItemCategory='Computer' and ProductCountry='United Stat'

Result:

ItemCode	ItemName	ItemCategory	ItemPrice
6	Apple Computer	Computer	400
8	Dell Computer	Computer	240
14	Ascer Computer	Computer	600

INSERT INTO SELECT Example in video

[Note: We have created and inserted values into sql table in previous topic. For more details Click here.
OR
Execute the follow command in your sql query window for executing above sql select where statement]
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Create Table ItemDetails

(

ItemCode int,

ItemName varchar(30),

ManufactureCompany varchar(50),

ExpiredDate date,

ItemSearialNo varchar (30),

ItemCategory varchar(30),

ItemQuantity int,

ItemPrice float,

ProductCountry varchar(30),

ReceivedDate date,

ItemImage image

)

Insert into ItemDetails values('1','Samsung Mobile','Samsung','12-10-2024','12-01-2050','S12345','Mobile','500','240','Korea','12-10-2025')
Insert into ItemDetails values('2','Samsung Pro','Samsung','12-10-2024','12-01-2050','SP1234','Mobile','100','300','Korea','12-10-2025')
Insert into ItemDetails values('3','Samsung Headphone','Samsung','12-10-2023','12-01-2053','SH2345','Headphone','50','50','Korea','12-10-2025')
Insert into ItemDetails values('4','iPhone','iPone','12-01-2024','12-01-2049','iP12345','Mobile','1000','500','United Stat','12-10-2025')
Insert into ItemDetails values('5','Apple Mobile','Apple','12-11-2024','12-01-2049','A12345','Mobile','200','300','United Stat','12-10-2025')
Insert into ItemDetails values('6','Apple Computer','Apple','12-02-2024','12-01-2050','A12343','Computer','200','400','United Stat','12-10-2025')
Insert into ItemDetails values('7','Apple Macbook','Apple','12-11-2024','12-01-2050','AM2345','Macbook','50','600','United Stat','12-10-2025')
Insert into ItemDetails values('8','Dell Computer','DELL','12-09-2024','12-01-2050','D02345','Computer','200','240','United Stat','12-10-2025')
Insert into ItemDetails values('9','Dell Laptop','DELL','12-09-2024','12-01-2050','DL2345','Laptop','30','240','United Stat','12-10-2025')
Insert into ItemDetails values('10','Apple Laptop','Apple','12-08-2024','12-01-2050','AL2345','Laptop','500','100','United Stat','12-10-2025')
Insert into ItemDetails values('11','Apple Headphone','Apple','12-12-2024','12-01-2050','AH2345','Headphone','50','20','United Stat','12-10-2025')
Insert into ItemDetails values('12','Sony Camera','Sony','12-11-2024','12-11-2050','SN2345','Camera','200','240','Japan','12-11-2025')
Insert into ItemDetails values('13','Sony Headphone','Sony','12-09-2024','12-01-2050','SNH345','Headphone','500','50','Japan','12-11-2025')
Insert into ItemDetails values('14','Ascer Computer','Ascer','12-09-2024','12-07-2050','AC2345','Computer','100','600','China','12-10-2025')
Insert into ItemDetails values('16','Borother Printer','Brother','12-09-2024','12-01-2050','B12345','Printer','200','200','China','12-10-2025')

Insert into ItemDetails values('19','Brother Multi Printer','Brother',GETDATE(),'2050-10-10','BM243','Photocopy',null,null,'India',GETDATE())

Insert into ItemDetails (ItemCode,ItemName,ManufactureCompany,ProductDate,ExpiredDate,ItemSearialNo,ItemCategory,ItemQuantity,ItemPrice,ProductCountry,ReceivedDate) values('17','Ascer Printer','Ascer','12-09-2024','12-07-2050','AP2345','Printer','200','150','China','12-10-2025')
Insert into ItemDetails (ItemCode,ItemName,ManufactureCompany,ProductDate,ExpiredDate,ItemSearialNo,ItemCategory,ItemQuantity,ItemPrice,ProductCountry,ReceivedDate) values('18','Brother Photocopy','Brother','12-08-2024','12-07-2050','BP2345','Printer','20','150','China','12-10-2025')

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++