You must Sign In to post a response.
  • Category: SQL Server

    How to pass the primary key id automatically as foreign key id in other table.?

    hi frnds..
    Here am having 2 tables names t1 and t2....t1 as columns as follows id as primary key auto generated,name,dept,desg....in table t2 also having the same columns as table t1 but only change i have not declared id as primary..here i have to pass value of primary key from the t1 and pass it as a foreign key for the table t2 automatically..wt i have to do...how can i pass this...any one please help me...

    Looking forward for your answers

    Thanks & Regards
    Prem Kumar.A
  • #651510
    HI,

    I believe you need like when you insert in t1 automatically add a row to t2. Is this right? This script may help you.


    create table t1(id int identity, name varchar(10))
    Go
    create table t2(id int , name varchar(10))
    Go
    CREATE TRIGGER [dbo].[NewIdInsertTrigger]
    ON [dbo].t1
    AFTER INSERT AS BEGIN
    SET NOCOUNT ON;
    insert into t2(id, name)
    select * from inserted
    END
    Go
    insert into t1 values( 'TestName')
    Go
    select * from t1
    Go
    select * from t2
    Go


    SQL Server Programmers and Consultants

  • #651512
    You need to create a column in table t2 and that column datatype will be same sa table t1 ID column and make that column as foreign key column using belwo sql

    Alter Table T2 Add Foreign Key (t2.columnName) references t1(ID));

  • #651515
    Or

    if you are using stored procedure to execute the table then you can get recent identity value usind

    SELECT SCOPE_IDENTITY()

    SQL Server Programmers and Consultants


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.