INSERT with SELECT vs INSERT with VALUES

Andrew Rissing

Rate me:

4.92/5 (6 votes)

30 Nov 2010CPOL

9.1K

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

Andrew Rissing

Architect

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.

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

INSERT with SELECT vs INSERT with VALUES

License

Comments and Discussions