IF Statement with Insert

Question

IF Statement with Insert

SQLSACT

SSC-Insane

Points: 21838
More actions
January 13, 2016 at 7:11 am

#310800

Hi All
SQL 2014...
DDL:
CREATE TABLE [dbo].[Table1](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [varchar](255) NULL,
[Col3] [varchar](1024) NULL,
[Col4] [varchar](1024) NULL,
[Col5] [char](20) NULL
) ON [PRIMARY]
DML:
IF @@SERVERNAME = 'CORRECT_SERVER_NAME'
BEGIN
INSERT INTO Table1
VALUES ('some_value',
'some_value',
'some_value',
'some_value')
END
ELSE
BEGIN
INSERT INTO Table1
VALUES (5,
'some_value',
'some_value',
'some_value',
'some_value')
END
Running the DML statement produces this error:
An explicit value for the identity column in table 'Table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.
This doesn't make any sense because the IF statement should valuate to TRUE and proceed with the first statement, the one that doesn't include an explicit value for the first column.
I would understand this error appearing if I ran the 2nd insert statement on its own.
What's funny is that when I comment out the 2nd insert statement and replace it with a PRINT 'TEST' command, then it works as it should.
Any ideas?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's a parse-time error. The *entire* batch gets parsed and bound before any part of it gets executed. The parser cannot execute code. Hence the error is thrown.

Oh, and the first one doesn't cause the error because it's a valid insert. It runs fine. Looks like if you don't specify columns and there's an identity and the number of columns specified matches the number of non-identity columns, the non-identity columns are the ones that get the data.

So

INSERT INTO Table1

VALUES ('some_value',

'some_value',

'some_value')

is equivalent to and interpreted as

INSERT INTO Table1 (Col2, Col3, Col4, Col5)

VALUES ('some_value',

'some_value',

'some_value')

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

One possible work around for the problem that Gail has identified is to replace the SQL in the IF blocks with dynamic SQL, which is not parsed until it's executed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

IF @@SERVERNAME = 'ORLANDO-SURFACE\SQL2012'

BEGIN

INSERT INTO Table1

VALUES ('some_value', 'some_value', 'some_value', 'some_value');

END;

ELSE

BEGIN

EXEC sys.sp_executesql

@statement = N'INSERT INTO Table1

SELECT 5,

''some_value'',

''some_value'';

';

END;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

SQLSACT

SSC-Insane

Points: 21838

January 14, 2016 at 2:11 am

#1851834

Thanks All