INSERT with SELECT vs INSERT with VALUES

Andrew Rissing

4.92/5 (6 votes)

30 Nov 2010CPOL

9.5K

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)