Insert Error

[btacy]

hi All,
i'm running a block of code to insert records after checking some criteria.

it gives this error "Cannot insert explicit value for identity column in table 'webx' when IDENTITY_INSERT is set to OFF."

i have tried to check what identity_insert is, so as to turn it on, to no avail.

looking forward to you advice.
thanks in advance

 

[jbenson001]

You are trying to insert a value into an identity column. Identity columns are used mainly as a primary key column and get generated automatically on an insert of a new row. Basically, you shouldn't be trying to enter a value into that column on your insert.

 

[SQLDenis]

list the columns, ommit the identity column since it will be generated

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[btacy]

thanks guys, worked perfectly.

 

[SQLSister]

I bet you did something like this

insert table1
select * from table2
[code]

what you need to do is 
[code]
insert table1 (field1, field2)
select field1, field2 from table2

when creating the list you would omit any fields that are autogenerated (identity fields, timestamp fields for exmaple and any that are calculated fields or that have a default value you always want placed inthe field (such as Date_entered field with a default of GetDate()))

Now to make life easier, you can drag the field names over from the object browser in QA. Simply expand thetable until all the fields are showing and then drag the word columns over to the query. This will bring in all the field names, then just delete the ones you don't want in the insert statement.

It is always better to specify fields in inserts.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

you only need to list the columns in the select part, you can skipt it from the table you insert into

this is fine

INSERT #INV_ITEMS2
SELECT COL1,DateStuff
FROM #INV_ITEMS

run this part to see what I mean

CREATE TABLE #INV_ITEMS (ID int identity,COL1 varchar(23),DateStuff datetime default 0)

INSERT #INV_ITEMS(COL1) VALUES('SUGAR')
INSERT #INV_ITEMS(COL1) VALUES('SALT')
INSERT #INV_ITEMS(COL1) VALUES('PEPPER')
INSERT #INV_ITEMS(COL1) VALUES('WASABI')

CREATE TABLE #INV_ITEMS2 (ID int identity,COL1 varchar(23),DateStuff datetime default 0)

INSERT #INV_ITEMS2
SELECT COL1,DateStuff
FROM #INV_ITEMS


SELECT * FROM #INV_ITEMS2

DROP TABLE #INV_ITEMS,#INV_ITEMS2

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Yes you can. I prefer not to as I think it is better to explicitly define which column from the select is going into which column from the insert. Then you never get someone accidentally inserting into the wrong column because they are in a different order in the two different tables. Perhaps I'm really picky about this because some of our clients have a habit of changing our feeds without notifying us first. It only takes putting the social security number into the field that shows how much a someone will get paid once to realize that you should use the column names. (yes this once happened where I work, not to me mercifully, and we did notice before anyone got paid that amount.)

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

all our user data is encrypted (of course) To know why, read this

http://sqlblog.com/blogs/denis_gobo/archive/2007/11/13/3310.aspx

So if I insert
this
QSISOZ9CGcIhCybjUCrlHtYwkeTxhQ94wilFTK/hjf9TifwEVLk=
instead of this
QSISOZ9CWciIYgiBoz0FGH+udhvVpTYlb0xeD7FamkiMcMLVse0=

into the wrong column I wouldn't know until we fired up an app

but I do agree with you that you should name the columns just to prevent those issues



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

I agree and we are moving towards encryption but aren't there yet. However, we will probably never get encrypted data from our all clients. Heck we have clients who can't even manage to give us the same data in the same format two times in a row. At least no one has yet tried to give us an Excel spreadsheet sorted by color code where we needed the information inthe color code and they didn't give us a column for that because they had sorted by color. I feel certain we will get one as soon as some of these folks figure out that the latest version of Excel will let you do that.

"NOTHING is more important in a database than integrity." ESquared

 

[LNBruno]

I'm sure Microsoft will have color-sort support in SSIS for SQL Server 2008?

< M!ke >
[small]Holidays cause stress. Crazy people crack under stress. Consider yourself warned.[/small]