Click here to Skip to main content
15,442,754 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 24 Nov 2010

Tagged as

Stats

21.6K views
6 bookmarked

INSERT with SELECT vs INSERT with VALUES

Rate me:
Please Sign up or sign in to vote.
3.67/5 (3 votes)
29 Nov 2010CPOL
INSERT with SELECT vs INSERT with VALUES
Assume we have a table Emp (EmpName VARCHAR(50), Age INT)

Query 1:

SQL
INSERT INTO Emp(EmpName, Age)
SELECT 'Ravi Kiran', 36


Query 2:

SQL
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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
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.

Comments and Discussions

 
GeneralReason for my vote of 1 There is no performance difference b... Pin
Dennis.D.Allen6-Dec-10 4:14
MemberDennis.D.Allen6-Dec-10 4:14 
Generalgud and deep study.... go on... Pin
nitin bhoyate29-Nov-10 22:20
Membernitin bhoyate29-Nov-10 22:20 
Generalutter nonsense Pin
Uwe Wittig30-Nov-10 9:28
MemberUwe Wittig30-Nov-10 9:28 
GeneralRe: utter nonsense Pin
Matt_au6-Dec-10 11:07
MemberMatt_au6-Dec-10 11:07 
GeneralNo, It's not Pin
thatraja27-Nov-10 23:06
professionalthatraja27-Nov-10 23:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.