sql-serversp-msforeachtable

SQL Server sp_MSforeachtable script error


Take the following script:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"; 

GO

CREATE TABLE _adminServices (
    [ServiceID] INT CHECK ([ServiceID] > 0) NOT NULL IDENTITY,
    [ServiceName] NVARCHAR(255) DEFAULT NULL,
    [ManagerStaffID] INT CHECK ([ManagerStaffID] > 0) DEFAULT NULL,
    [ODMStaffID] INT CHECK ([ODMStaffID] > 0) DEFAULT NULL,
    [ReferralInactivityDays] INT DEFAULT NULL,
    [TargetSupportHours] INT DEFAULT NULL,
    [ShowInLists] SMALLINT NOT NULL DEFAULT '1',
    [RecordEntryDate] DATETIME2(0) DEFAULT NULL,
    [RecordModDate] DATETIME2(0) DEFAULT NULL,
    PRIMARY KEY ([ServiceID])
) ;

CREATE INDEX [ManagerStaffID] ON _adminServices ([ManagerStaffID]);
CREATE INDEX [ODMStaffID] ON _adminServices ([ODMStaffID]);
CREATE INDEX [ShowInLists] ON _adminServices ([ShowInLists]);

GO

EXEC sp_MSforeachtable @command1 = 'IF  (
                                            select  COUNT(TABLE_NAME)
                                            from    INFORMATION_SCHEMA.COLUMNS
                                            where   COLUMNPROPERTY(object_id(TABLE_SCHEMA+''.''+TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1
                                            AND     TABLE_SCHEMA+''.''+TABLE_NAME = ''?''
                                        ) = 1
BEGIN
    SET IDENTITY_INSERT ? ON;
END;';

GO

INSERT INTO _adminServices (ServiceID, ServiceName, ManagerStaffID, ODMStaffID, ReferralInactivityDays, TargetSupportHours, ShowInLists, RecordEntryDate, RecordModDate) VALUES
(1, 'Service 1', 16, 18, 0, NULL, 1, '2017-07-21 11:59:56', '2017-10-25 09:38:02');

GO

When I execute the aforementioned script in SSMS I get the following error:

Msg 544, Level 16, State 1, Line 36 Cannot insert explicit value for identity column in table '_adminServices' when IDENTITY_INSERT is set to OFF.

Can anyone tell me why?

EDIT: My goal is the following: I have multiple tables and multiple inserts. For the Inserts I have the scripts. Since I don't want to write the SET IDENTITY_INSERT ON and OFF for every table since I just have the INSERT INTO queries on a file, I want a way to do the following:

  1. Delete all the tables in the DB

  2. Create all the tables (I have the SQL for this)

  3. Set all the required identities to ON

  4. Run the Inserts (I have the SQL for this)

  5. Set all the required identities to OFF


Solution

    1. sp_MSforeachtable run on another session in relation to your insert
    2. At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

    General scheme of inserting the original value:

    1. CREATE TABLE

    2. SET IDENTITY INSERT ON

    3. INSERT VALUES

    4. SET IDENTITY INSERT OFF

    If you table not have single structure sp_MSforeachtable you will not be suitable