CODEWITHSUNDEEP
sqlsql-serverinsert
ove
ove

Reputation: 3202

Inserting newly created ID into two tables

Below the script does not work but only working if it inserting a single row.

The documentId should not be declared on first line but from the retrieve from DOCUMENTS table.

I read about SCOPE_IDENTITY() but not sure how to apply it.

[dbo].[DOCUMENTS].DOCUMENT_ID is a primary key with auto increment int.

What is required is for every row inserted into DOCUMENTS table, it automatically insert and add it to the last row and use the newly created DOCUMENT_ID to populate @documentId defined in two table below.

declare @documentId int;
set @documentId = (SELECT MAX(DOCUMENT_ID) FROM dbo.DOCUMENTS)+ 1

INSERT INTO [dbo].[DOCUMENTS] (
       DOCUMENT_ID
      ,DOCUMENT_TYPE
      ,DOCUMENT_REF
      ,DOCUMENT_NOTE
      ,DOCUMENT_DATE
      ,LAST_UPDATE_ID
      ,LAST_UPDATE_DATE
      ,DOCUMENT_FUNCTION_CODE
      ,BATCH_ID
      ,AUDIT_XML
      ,AUDIT_USER
      ,REMINDER_DATE)
     SELECT
     @documentId,
     NULL,
     NULL,
     'MIGRATED FROM BRING UP NOTES ' + CAST(GETDATE() AS VARCHAR(20)) + ' ' + sr.BRINGUP_NOTES,
     GETDATE(),
     NULL,
     NULL,
     NULL,
     NULL,
     NULL,
     NULL,
     sr.BRINGUP_DATE
     FROM [dbo].[SERVICE_REQUESTS] sr

----- Insert into another table ------------
INSERT INTO [dbo].[SERVICE_REQUEST_ITEM_DOCS] (
  [SERVICE_REQUEST_ID]
      ,[SERVICE_REQUEST_ITEM_SEQNO]
      ,[DOCUMENT_ID]
      ,[LAST_UPDATE_ID]
      ,[LAST_UPDATE_DATE]
      ,[COVER_LETTER])
     SELECT 
     sr.SERVICE_REQUEST_ID,
     1,
     @documentId,
     0,
     GETDATE(),
     0
     FROM [dbo].[SERVICE_REQUESTS] sr

Upvotes: 0

Views: 89

Answers (3)

J45
J45

Reputation: 396

Assuming that DOCUMENT_ID is an identity column you can use an output clause to get the new ID, for example:

DECLARE @DOCUMENT_ID int
DECLARE @TID TABLE (DOCUMENT_ID int)

INSERT INTO SERVICE_REQUEST_ITEM_DOCS (Col1, Col2, Col3)
    OUTPUT inserted.DOCUMENT_ID INTO @TID(DOCUMENT_ID)
    VALUES ('val1', 'val2', 'val3')

SELECT @DOCUMENT_ID = DOCUMENT_ID FROM @TID

Upvotes: 0

Ravi
Ravi

Reputation: 1172

First of all you cant insert auto increment column without setting its identity on

    declare @documentId int;
    --set @documentId = (SELECT MAX(DOCUMENT_ID) FROM dbo.DOCUMENTS)+ 1

    INSERT INTO [dbo].[DOCUMENTS] (DOCUMENT_TYPE      ,DOCUMENT_REF      ,DOCUMENT_NOTE      ,DOCUMENT_DATE      ,LAST_UPDATE_ID      ,LAST_UPDATE_DATE      ,DOCUMENT_FUNCTION_CODE      ,BATCH_ID      ,AUDIT_XML      ,AUDIT_USER      ,REMINDER_DATE)
         SELECT     NULL,     NULL,     'MIGRATED FROM BRING UP NOTES ' + CAST(GETDATE() AS VARCHAR(20)) + ' ' + sr.BRINGUP_NOTES,     GETDATE(),     NULL,     NULL,     NULL,     NULL,     NULL,     NULL,     sr.BRINGUP_DATE
         FROM [dbo].[SERVICE_REQUESTS] sr

    set @documentid=@@identity() 

--or      set @documentid=SCOPE_Identity()  


    ----- Insert into another table ------------
    INSERT INTO [dbo].[SERVICE_REQUEST_ITEM_DOCS] (
      [SERVICE_REQUEST_ID]
          ,[SERVICE_REQUEST_ITEM_SEQNO]
          ,[DOCUMENT_ID]
          ,[LAST_UPDATE_ID]
          ,[LAST_UPDATE_DATE]
          ,[COVER_LETTER])
         SELECT 
         sr.SERVICE_REQUEST_ID,
         1,
         @documentId,
         0,
         GETDATE(),
         0
         FROM [dbo].[SERVICE_REQUESTS] sr

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

As you have Doucment_ID as primary key with auto increment value, you dont have to pass variable in your 1st Insert statement. I have made some correction in your insert statements, check out my answer.

declare @documentId int;


INSERT INTO [dbo].[DOCUMENTS] (
      DOCUMENT_TYPE
      ,DOCUMENT_REF
      ,DOCUMENT_NOTE
      ,DOCUMENT_DATE
      ,LAST_UPDATE_ID
      ,LAST_UPDATE_DATE
      ,DOCUMENT_FUNCTION_CODE
      ,BATCH_ID
      ,AUDIT_XML
      ,AUDIT_USER
      ,REMINDER_DATE)
     SELECT
     NULL,
     NULL,
     'MIGRATED FROM BRING UP NOTES ' + CAST(GETDATE() AS VARCHAR(20)) + ' ' + sr.BRINGUP_NOTES,
     GETDATE(),
     NULL,
     NULL,
     NULL,
     NULL,
     NULL,
     NULL,
     sr.BRINGUP_DATE
     FROM [dbo].[SERVICE_REQUESTS] sr

Set @documentId = SCOPE_IDENTITY()

----- Insert into another table ------------
INSERT INTO [dbo].[SERVICE_REQUEST_ITEM_DOCS] (
  [SERVICE_REQUEST_ID]
      ,[SERVICE_REQUEST_ITEM_SEQNO]
      ,[DOCUMENT_ID]
      ,[LAST_UPDATE_ID]
      ,[LAST_UPDATE_DATE]
      ,[COVER_LETTER])
     SELECT 
     sr.SERVICE_REQUEST_ID,
     1,
     @documentId,
     0,
     GETDATE(),
     0
     FROM [dbo].[SERVICE_REQUESTS] sr

Upvotes: 1

Related Questions