Error when trying to compile SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error when trying to compile SQL statement

Author

Topic

wleonard
Starting Member

30 Posts

Posted - 2011-01-20 : 17:01:06

SELECT GETDATE ()

DECLARE @presentdate DATETIME

DECLARE @futuredate DATETIME

SET @futuredate = (SELECT startdate FROM Shipping_Promo_Date_Select_Test)

if @futuredate = (SELECT GETDATE ())
DELETE FROM Shipping_Discount_Test
INSERT INTO Shipping_Discount_Test SELECT * FROM Shipping_Discount_Future_Test

I receive the following error:

Msg 8101, Level 16, State 1, Line 11
An explicit value for the identity column in table 'Shipping_Discount_Test' can only be specified when a column list is used and IDENTITY_INSERT is ON.

What does this mean and how do I correct it?

Will Leonard

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-20 : 17:05:47

If you want to copy the identity values exactly as they are, then you'll need to add the IDENTITY_INSERT ON option to your script. Don't forget to turn it OFF when you are done!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-01-20 : 17:08:17

It means your Shipping_Discount_Test table has an IDENTITY column. Two ways of getting around this:

SET IDENTITY_INSERT Shipping_Discount_Test ON;

Then rather than inserting using SELECT * FROM... provide the entire list of columns that will be going to the table.

Another way of doing this is since you're deleting the entire table anyway, drop the table and use INSERT INTO to recreate it from your Shipping_Discount_Future_Test data. However only do this if the structures of the two tables match.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources