1

I have something like this:

CREATE TABLE [dbo].[table1] 
(
    [id1] [int] IDENTITY(1,1) NOT NULL,
    [data] [varchar](255) NOT NULL,
    CONSTRAINT [PK_table1] PRIMARY KEY(id1) 
)

CREATE TABLE [dbo].[table2] 
(
    [id2] [int] IDENTITY(1,1) NOT NULL,
    [id1] [int] ,
    
    CONSTRAINT [PK_table2] PRIMARY KEY (id2)
    CONSTRAINT [FK_table2] FOREIGN KEY(id1) REFERENCES Table1
)

I want to add values to both the tables using a procedure. I'm not adding any key values just data values.

If I use INSERT INTO to add data into Table 1, its primary key will be autoincremented. I will also be incrementing the Table 2 in the same procedure.

I want that the autoincremented primary key of Table 1 should automatically be updated as foreign key in Table 2 when I run that procedure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Snoke
  • 75
  • 7

2 Answers2

0

You need to do something like this:

CREATE PROCEDURE dbo.InsertData (@data VARCHAR(255))
AS
BEGIN
    -- Insert row into table1
    INSERT INTO dbo.Table1 (data) VALUES (@data);
    
    -- Capture the newly generated "Id1" value
    DECLARE @NewId1 INT;
    SELECT @NewId1 = SCOPE_IDENTITY();
    
    -- Insert data into table2
    INSERT INTO dbo.table2 (Id1) VALUES (@NewId1);
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

I don't know if I understand what do you want to do but I think you can do something like this:

INSERT INTO table1 (data) VALUES 'mydata'
DECLARE @LastKey INT
SET @LastKey = SCOPE_IDENTITY() -- FOR SQL SERVER, OR LAST_INSERT_ID() FOR MYSQL
INSERT INTO table2 (data) VALUES @LastKey
Simone Urbani
  • 130
  • 1
  • 5
  • You need to put the values that you insert into parenthesis - like this: `VALUES ('mydata')` etc. – marc_s Jun 04 '21 at 15:08