Assume we have a table Emp (EmpName VARCHAR(50), Age INT)
Query 1:
INSERT INTO Emp(EmpName, Age)
SELECT 'Ravi Kiran', 36
Query 2:
INSERT INTO Emp(EmpName, Age)
VALUES ('Ravi Kiran', 36)
Both the queries will achieve the same result. But which one is better? Run the below code:
SET NOCOUNT ON
PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO Emp(EmpName, Age)
SELECT 'Ravi Kiran', 36
PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO Emp(EmpName, Age)
VALUES ('Ravi Kiran', 36)
PRINT CONVERT(VARCHAR, GETDATE(), 121)
The result is:
2010-11-23 23:04:08.617
2010-11-23 23:04:08.640
2010-11-23 23:04:08.640
The INSERT...SELECT took 23 milliseconds where as INSERT...VALUES took 0 milliseconds. Now you know!
So, if you want to insert a single record in a table, use INSERT...VALUES instead of INSERT...SELECT.
Ravikiran Malladi is a senior software professional in Microsoft technologies. He has global IT experience of over 10 years and has compulsive habit of writing tools, particularly for the projects he works on. He has vast experience in the roles of senior developer, technical analyst, technical architect besides in technical lead roles.
His hobbies include chess, music, cricket, web sites along side writing technical articles and technical blogs.