SQL Server에서 IDENTITY_INSERT가 ON 또는 OFF로 설정되어 있는지 어떻게 확인합니까?

SQL Server에서 IDENTITY_INSERT가 ON 또는 OFF로 설정되어 있는지 어떻게 확인합니까?

저는 이것을 검색했지만, 그것이 보이는 스레드에는 질문을 이해하지 못하는 사람들의 대답이 있는 경향이 있었습니다.

다음 구문을 사용합니다.

SET IDENTITY_INSERT Table1 ON

다음과 같은 작업을 수행하는 방법:

GET IDENTITY_INSERT Table1

그러나 데이터베이스의 데이터나 설정에 대해 이 정보를 얻기 위해 어떤 작업도 수행하고 싶지 않습니다.감사합니다!

부터SET IDENTITY_INSERT세션에 민감하며 어딘가에 저장하지 않고 버퍼 수준에서 관리됩니다.이는 우리가 확인할 필요가 없다는 것을 의미합니다.IDENTITY_INSERT현재 세션에서 이 키워드를 사용하지 않는 상태입니다.

죄송합니다. 도움이 안 됩니다.

그래도 좋은 질문입니다 :)

출처: 여기

업데이트 제가 링크한 IMO 사이트에서도 볼 수 있듯이, 이를 위한 방법이 있을 수 있습니다. 유용하기에는 너무 많은 노력이 필요합니다.

if

(select max(id) from MyTable) < (select max(id) from inserted)

--Then you may be inserting a record normally

BEGIN
    set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE
END

ELSE --You definitely have IDENTITY_INSERT on.  Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway


BEGIN
.... Code that shouldn't run with IDENTITY_INSERT on
END

요약:

• Nathan의 솔루션은 가장 빠릅니다.

  SELECT OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity');
  

  • API 래퍼를 사용할 때 행을 확인하는 것만으로 전체 검사를 줄일 수 있습니다.예를 들어 C#을 사용하는 경우SqlDataReaders소유물HasRows다음과 같은 쿼리 구조:

    SELECT CASE OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity')
           WHEN 1 THEN '1' ELSE NULL END
    

• Ricardo의 솔루션은 더 많은 유연성을 제공하지만 Column의 ID 이름이 필요합니다.

  SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MyTable', 'U') 
                          AND name = 'MyTableIdentityColumnName';
  

• Bogdan Bodanov 솔루션, 사용try/catch또한 작동할 수 있지만, 추가 검사는 예외 처리를 다음의 경우로 제한해야 합니다.IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable';

아래 코드를 사용하여 identity_insert가 켜져 있는지 여부와 어떤 테이블에 대해 설정되어 있는지 확인할 수 있습니다.

declare @tableWithIdentity varchar(max) = '';
SET IDENTITY_INSERT ExampleTable ON

begin try
  create table #identityCheck (id int identity(1,1))
  SET IDENTITY_INSERT #identityCheck ON
  drop table #identityCheck
end try
begin catch
  declare @msg varchar(max) = error_message()
  set @tableWithIdentity= @msg;
  set @tableWithIdentity = 
  SUBSTRING(@tableWithIdentity,charindex('''',@tableWithIdentity,1)+1, 10000)

  set @tableWithIdentity = SUBSTRING(@tableWithIdentity,1, charindex('''',@tableWithIdentity,1)-1)

  print @msg;
  drop table #identityCheck
end catch

if @tableWithIdentity<>''
begin
  print ('Name of table with Identity_Insert set to ON: ' + @tableWithIdentity)
end
else
begin
  print 'No table currently has Identity Insert Set to ON'
end 

IDENTITY_INSERT를 설정할 때 오류가 발생하지 않도록 다른 테이블에 대해 IDENTITY_INSERT를 해제하려는 경우 다음 항목도 사용할 수 있습니다.다른 사람들이 이 스레드에서 말했듯이 IDENTITY_INSERT는 직접적인 가시성이 없는 세션 설정입니다.그러나 ID가 있는 테이블에 대해 IDENTITY_INSERT가 켜져 있는지 여부에 관계없이 SET IDENTITY_INSERT OFF가 오류가 발생하지 않는다는 흥미로운 발견을 했습니다.그래서 데이터베이스에 ID가 있는 모든 테이블에 대해 SET IDENTITY_INSERT... OFF를 호출할 수 있다는 생각이 들었습니다.마치 무차별적인 솔루션처럼 느껴지지만, 다음의 동적 SQL 블록이 매우 효과적이라는 것을 알게 되었습니다.

---- make sure IDENTITY_INSERT is OFF ----
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = CAST((SELECT 'SET IDENTITY_INSERT ' + 
             QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + 
             QUOTENAME(t.name) + ' OFF' + CHAR(10)
             FROM sys.columns c 
             JOIN sys.tables t ON t.object_id = c.object_id
             WHERE c.is_identity = 1 
             ORDER BY 1 FOR XML PATH('')) AS NVARCHAR(MAX))
EXEC sp_executesql @cmd

아주 좋은 질문입니다.저도 같은 문제가 있습니다.TRY/CATCH를 사용하여 IDENTITY_INSERT를 재설정해 볼 수 있습니까?예를 들어 작업을 수행하지만 작업이 완료되었는지 확인할 수 없으며 IDENTITY_INSERT가 OFF로 설정되어 있습니다.

시도하지 않는 이유:

BEGIN TRY 
...
END TRY 
BEGIN CATCH
SET IDENTITY_INSERT table OFF;
END CATCH;

또한 이것이 제대로 작동하는지 확신할 수 없지만 추가만 하는 것으로 보입니다.SET IDENTITY_INSERT ... OFF오류를 반환하지 않았습니다.그래서 당신은 마지막에 만약을 대비해서 설정할 수 있습니다.SET IDENTITY_INSERT ... OFF.

세션 변수에 대해 알고 싶은 경우...좋은 질문입니다만, 이 정보가 어디에 유용한지 모르겠습니다.삽입물에 대한 정상적인 테이블 응답을 확인하기 위한 정상적인 실행에서는 이것이 작동해야 합니다!

지정된 테이블에 ID 삽입이 있는지 여부만 알고자 하는 경우:

select is_identity
from sys.columns
where object_id = OBJECT_ID('MyTable', 'U') and name = 'column_Name'

아니면...결과에 따라 무언가를 실행하려면 이 옵션을 사용합니다.

if exists (select *
from sys.columns
where object_id = OBJECT_ID('MyTable', 'U') and is_identity = 1)
... your code considering identity insert
else
... code that should not run with identity insert

재미있게 보내!

여기 제 해결책이 있습니다.@jmoreno의 대답과 매우 유사합니다.

당신은 그것을 이렇게 부를 것입니다.

DECLARE @IdentityInsert VARCHAR(20)
EXEC dbo.GetIdentityInsert 'YourDb', 'YourSchema', 'YourTable', @IdentityInsert OUT
SELECT @IdentityInsert 

그러면 IDENTITY_INSERT 열 이름의 1행 레코드 집합이 반환됩니다. 이 레코드 집합은 ON, OFF 또는 NO_IDENITY일 수 있습니다(지정된 테이블에 ID 열이 없는 경우).또한 출력 파라미터 @Identity를 설정합니다.삽입합니다. 원하는 방법으로 코드를 조정할 수 있습니다.

이것을 사용자 정의 기능으로 만들면 좋겠지만, 안타깝게도 저는 트라이를 피할 방법을 찾지 못했습니다.사용자 정의 함수에서 사용할 수 없는 CATCH 블록입니다.

-- ================================================================================
-- Check whether the table specified has its IDENTITY_INSERT set to ON or OFF.
-- If the table does not have an identity column, NO_IDENTITY is returned.
-- Tested on SQL 2008.
-- ================================================================================
CREATE PROCEDURE dbo.GetIdentityInsert

      @dbname sysname
    , @schemaname sysname
    , @table sysname
    , @IdentityInsert VARCHAR(20) OUTPUT

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @OtherTable nvarchar(max)
    DECLARE @DbSchemaTable nvarchar(max)

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorNumber INT;
    DECLARE @object_id INT;

    SET @DbSchemaTable = @dbname + '.' + @schemaname + '.' + @table

    SET @object_id = OBJECT_ID(@DbSchemaTable)
    IF @object_id IS NULL
    BEGIN
        RAISERROR('table %s doesn''t exist', 16, 1, @DbSchemaTable)
        RETURN
    END


    BEGIN TRY

        SET @object_id = OBJECT_ID(@DbSchemaTable)

        IF OBJECTPROPERTY(@object_id,'TableHasIdentity') = 0
        BEGIN
            SET @IdentityInsert = 'NO_IDENTITY'
        END
        ELSE
        BEGIN
            -- Attempt to set IDENTITY_INSERT on a temp table. This will fail if any other table
            -- has IDENTITY_INSERT set to ON, and we'll process that in the CATCH
            CREATE TABLE #GetIdentityInsert(ID INT IDENTITY)
            SET IDENTITY_INSERT #GetIdentityInsert ON
            SET IDENTITY_INSERT #GetIdentityInsert OFF
            DROP TABLE #GetIdentityInsert

            -- It didn't fail, so IDENTITY_INSERT on @table must set to OFF
            SET @IdentityInsert = 'OFF'
        END
    END TRY


    BEGIN CATCH

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorNumber = ERROR_NUMBER();

        IF @ErrorNumber = 8107  --IDENTITY_INSERT is already set on a table
        BEGIN
            SET @OtherTable = SUBSTRING(@ErrorMessage, CHARINDEX(char(39), @ErrorMessage)+1, 2000)
            SET @OtherTable = SUBSTRING(@OtherTable, 1, CHARINDEX(char(39), @OtherTable)-1)

            IF @OtherTable = @DbSchemaTable 
            BEGIN
                -- If the table name is the same, then IDENTITY_INSERT on @table must be ON
                SET @IdentityInsert = 'ON'
            END
            ELSE
            BEGIN
                -- If the table name is different, then IDENTITY_INSERT on @table must be OFF
                SET @IdentityInsert =  'OFF'
            END
        END
        ELSE
        BEGIN
            RAISERROR (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState);
            --THROW     Use this if SQL 2012 or higher
        END

    END CATCH

    SELECT [IDENTITY_INSERT] = @IdentityInsert
END
GO

ObjectProperty 메서드를 사용하여 테이블에 ID가 있는지 확인할 수도 있습니다.

DECLARE @MyTableName nvarchar(200)
SET @MyTableName = 'TestTable'
SELECT CASE OBJECTPROPERTY(OBJECT_ID(@MyTableName), 'TableHasIdentity') 
WHEN 1 THEN 'has identity' 
ELSE 'no identity columns' 
END as HasIdentity

언급URL : https://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server