Identity Insert 1

[travisbrown]

What's wrong with this statement?

SET IDENTITY_INSERT  dbo.tbl_tickets ON
INSERT INTO tbl_tickets
SELECT ticket_id, ticket_number, ticket_district, NULL, ticket_start_ts, ticket_user, ticket_notes, ticket_emergent, ticket_called_manager
FROM COPs.dbo.tblDispatchTicket
SET IDENTITY_INSERT dbo.tbl_tickets OFF

I get
"An explicit value for the identity column in table 'tbl_tickets' can only be specified when a column list is used and IDENTITY_INSERT is ON."

However, I thought I was turning ID_INSERT on.

 

[travisbrown]

Oh, I just reread the error. Is it where I have to specify the input columns in parentheses following the input table name?

 

[travisbrown]

Ah, see what happens when I don't panic and read the error?

SET IDENTITY_INSERT  dbo.tbl_tickets ON
INSERT INTO tbl_tickets
   (ticket_id, ticket_number, ticket_group, ticket_class, ticket_start_ts, ticket_dispatcher, ticket_dispatcher_note, ticket_emergent, ticket_call_manager)
SELECT ticket_id, ticket_number, ticket_district, NULL, ticket_start_ts, ticket_user, ticket_notes, ticket_emergent, ticket_called_manager
FROM COPs.dbo.tblDispatchTicket
SET IDENTITY_INSERT dbo.tbl_tickets OFF

 

[ESquared]

now have fun accidentally leaving identity_insert on for some random table in your database... then try to set it on for another.

Now you have to find out which one has it on, or run script to set it off for all of them.