locked
How do I generate a primary key for a table while inserting a Primary key from 1 table to another table as foreign key. RRS feed

  • Question

  • Before you read the details of my problem I just want to point out that I am very new to t-sql so the approach I have taken to solve my problem may me complety wrong.  

    I have a table that has the following structure:

    Staff Table:

    P_STF (PK, int, not null)

    LotsOfOtherdatacolumns(varchar(22, not null))


    This table currently has lots of data

    eg of table and data:

    P_STF                                      LotsOfOtherdatacolumns

    1                                              Tom

    2                                              Harry   

    3                                              Jhon

    .                                               .

    .                                               .          

    1000                                         Roger


    I have another table that currently does not have any data but has the following structure:

    STFWKE Table:

    P_SWK(PK, int, not null) --- the Identity for this column is set to NO

    F_STF(FK, int, not null) --- this is meant to hold the foreign key from Staff table


    I want to do 2 things:

    1. Get each P_STF field value from Staff Table and insert it into STFWKE as F_STF.

    2. Insert a Primary key for STFWKE table without setting the identity for this column to YES.


    eg. of desired outcome:

    P_SWK                                    F_STF

    2                                              1

    3                                              2         

    4                                              3

    .                                               .

    .                                               .          

    999                                           1000


    I have done the following so far: 

    --@counter is a counter to store primary key for the stfwke table

    declare @tableLength int;

    declare @counter int;

    select @tableLength=COUNT P_STF FROM staff;

    set @counter=1;

     

    -- populate STFWKE table with primary key from staff table.

    declare @nextStaffFKey int;

    set @nextStaffFKey = select p_stf from staff;

     

    while (@tableLength>0)

    Begin

                set @nextStaffFKey = Select distinct p_stf from staff;

                INSERT INTO STFWKE ( P_SWE,F_STF)

        values (@counter,@nextStaffFKey)

                set @counter= @counter +1;

                set @tableLength=@tableLength -1;

    End

     

    I get an error when I run this because the select statement return a list and not an int.

    What is the best way to achive this?

     

     

     

    Tuesday, June 16, 2009 5:44 AM

Answers

  • presume you are runing SQL 2005 and above.

    declare @counter int;
    set @counter=1;
    insert into  STFWKE ( P_SWE,F_STF)
    select row_number() over (order by P_STF)+ @counter, P_STF from staff

    HTH,
    Huiyong

    PS: if P_SWE is a typo, try P_SWK.
    Tuesday, June 16, 2009 6:05 AM

All replies

  • presume you are runing SQL 2005 and above.

    declare @counter int;
    set @counter=1;
    insert into  STFWKE ( P_SWE,F_STF)
    select row_number() over (order by P_STF)+ @counter, P_STF from staff

    HTH,
    Huiyong

    PS: if P_SWE is a typo, try P_SWK.
    Tuesday, June 16, 2009 6:05 AM
  • Thanks for your help.
    Friday, June 26, 2009 5:18 AM