Click here to Skip to main content
15,513,652 members
Articles / Database Development / SQL Server
Alternative
Tip/Trick
Posted 30 Nov 2010

Tagged as

Stats

8.5K views
2 bookmarked

INSERT with SELECT vs INSERT with VALUES

Rate me:
Please Sign up or sign in to vote.
4.92/5 (6 votes)
30 Nov 2010CPOL
You might want to do some further testing... It seems if you perform the following, the values are actually the same:SET NOCOUNT ONDECLARE @TMP TABLE( EmpName VARCHAR(50), Age INT)PRINT CONVERT(VARCHAR, GETDATE(), 121)INSERT INTO @TMP(EmpName, Age)SELECT 'Ravi Kiran',...
You might want to do some further testing... It seems if you perform the following, the values are actually the same:

SQL
SET NOCOUNT ON

DECLARE @TMP TABLE
(
  EmpName VARCHAR(50), Age INT
)

PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO @TMP(EmpName, Age)SELECT 'Ravi Kiran', 36
PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO @TMP(EmpName, Age)VALUES ('Ravi Kiran', 36)
PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO @TMP(EmpName, Age)SELECT 'Ravi Kiran', 36
PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO @TMP(EmpName, Age)VALUES ('Ravi Kiran', 36)
PRINT CONVERT(VARCHAR, GETDATE(), 121)

SET NOCOUNT OFF

Secondly, if you flip the order, the results match the original query:

SQL
SET NOCOUNT ON

DECLARE @T2MP TABLE
(
  EmpName VARCHAR(50), Age INT
)

PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO @T2MP(EmpName, Age)VALUES ('Ravi Kiran', 36)
PRINT CONVERT(VARCHAR, GETDATE(), 121)
INSERT INTO @T2MP(EmpName, Age)SELECT 'Ravi Kiran', 36
PRINT CONVERT(VARCHAR, GETDATE(), 121)

SET NOCOUNT OFF

Both of these contradict the results you concluded.

License

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


Written By
Architect
United States United States
Since I've begun my profession as a software developer, I've learned one important fact - change is inevitable. Requirements change, code changes, and life changes.

So..If you're not moving forward, you're moving backwards.

Comments and Discussions

 
GeneralReason for my vote of 5 Agree better proof needed, and I kno... Pin
Dennis.D.Allen6-Dec-10 5:12
Dennis.D.Allen6-Dec-10 5:12 

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.