-2
Insert into HWT FINAL. DO. TBL_AT_INOUT
(ID)
SELECT ID FROM TBL_AT_INOUT_20171208
WHERE ID=904017

Getting error while executing

James Z
  • 12,209
  • 10
  • 24
  • 44
sahil ehsan
  • 21
  • 1
  • 3
  • Kindly stop yelling first. No one likes all caps. Why are you trying to do an insert of *only an ID value* into a table in the first place? This definitely sounds like an XY problem. – Jacob H Dec 08 '17 at 13:40
  • 2
    Have you tried SET IDENTITY_INSERT **ON** before the insert? As per the docs https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql – gbn Dec 08 '17 at 13:40
  • @JacobH there are use cases for this, just do it correctly with it set to ON – gbn Dec 08 '17 at 13:42
  • Did you even google at all? https://stackoverflow.com/questions/12563522/how-can-i-insert-identity-manually – Tab Alleman Dec 08 '17 at 13:46
  • @gbn yes I tried set IDENTITY_INSERT table name on But I got another error. – sahil ehsan Dec 08 '17 at 13:48
  • 1
    @gbn Oh sure there are use cases. But if someone doesn't know what an Identity insert is and they're *only inserting an identity column*, my hunch is that this isn't one of those use cases. Hence the XY problem. – Jacob H Dec 08 '17 at 13:52
  • @sahilehsan what is the other error? – gbn Dec 08 '17 at 13:54

1 Answers1

0

This is what you're looking for:

IF object_id(N'tempdb..#test_table_01', N'U') IS NOT NULL
    DROP TABLE #test_table_01;

go

CREATE TABLE #test_table_01
  (
       [id]     INT IDENTITY(1, 1)
       , [name] SYSNAME
  );

--
-- fail method
-------------------------------------------------
BEGIN
    BEGIN try
        INSERT INTO #test_table_01
                    ([id]
                     , [name])
        VALUES      (1,N'Lucy');
    END try
    BEGIN catch
        SELECT N'This throws an error' AS [result]
               , ERROR_MESSAGE()       AS [error_message];
    END catch;

    SELECT N'No records were inserted' AS [note]
           , [id]
           , [name]
    FROM   #test_table_01;
END;


--
-- success method
-------------------------------------------------
BEGIN
    BEGIN try
        SET IDENTITY_INSERT #test_table_01 ON;

        INSERT INTO #test_table_01
                    ([id]
                     , [name])
        VALUES      (1,N'Lucy');

        -- 
        -- REMEMBER TO SET IDENTITY_INSERT BACK OFF!!!
        SET IDENTITY_INSERT #test_table_01 OFF;
    END try
    BEGIN catch
        SELECT N'This allows the insert' AS [result]
               , ERROR_MESSAGE()         AS [error_message];
    END catch;

    SELECT N'Records were inserted as desired!' AS [note]
           , [id]
           , [name]
    FROM   #test_table_01;
END;
Katherine Elizabeth Lightsey
  • 853
  • 5
  • 12
  • Remove the name column to make it like the original question.l does it still work? – gbn Dec 08 '17 at 14:10
  • I try to usually include test code for that reason, so you can try it yourself and use it as a prototype for building what you need. But yes, it does work. The SET IDENTITY_INSERT (https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql) function is what you are looking for. Just remember to set it back off after you've inserted your values. – Katherine Elizabeth Lightsey Dec 08 '17 at 14:15
  • 1
    I'm not the OP. But I was suggesting that you make it match what OP asked, That is all. – gbn Dec 08 '17 at 14:29