Split table and insert with identity link

Question

Hi.. I have 3 tables similar to the sctructure below

    CREATE TABLE [dbo].[EmpBasic](
        [EmpID] [int] IDENTITY(1,1) NOT NULL Primary Key,
        [Name] [varchar](50),
        [Address] [varchar](50)
        )


    CREATE TABLE [dbo].[EmpProject](
        [EmpID] [int] NOT NULL primary key,  // referencing column with EmpBasic
        [EmpProject] [varchar](50)  )



    CREATE TABLE [dbo].[EmpFull_Temp](
        [ObjectID] [int] IDENTITY(1,1) NOT NULL Primary Key,
        [T1Name] [varchar](50) ,
        [T1Address] [varchar](50) ,
        [T1EmpProject] [varchar](50) 
            )

The EmpFull_Temp table has the records with a dummy object ID column... I want to populate the first 2 tables with the records in this table... But with EmpID as a reference between the first 2 tables.

I tried this in a stored procedure...

    Create Table #IDSS (EmpID bigint, objID bigint)

    Insert into EmpBasic
        output Inserted.EmpID, EmpFull_Temp.ObjectID
        into #IDSS
        Select T1Name, T1Address from EmpFull_Temp
            Where ObjectID < 106


    Insert into EmpProject
        Select A.EmpID, B.T1EmpProject from #IDSS as A, EmpFull_Temp as B
            Where A.ObjID = B.ObjectID

But it says.. The multi-part identifier "EmpFull_Temp.ObjectID" could not be bound.

Could you please help me in achieving this...

Edit : There is no guarantee that [Name]+[Address] would be unique across [EmpBasic] Table

Answer 1

The solution to this problem depends on whether the "parent" table (i.e. the one with the IDENTITY column) has a natural key (i.e. one or more fields which, when combined, are guaranteed to be unique, other than the surrogate primary key).

For example, in this case, is the combinaton of Name and Address aways going to be unique?

If the answer is yes then you can simply insert into EmpBasic without bothering to output and store the generated IDs. You can then insert into EmpProject joining back on to EmpBasic using the natural key (e.g. name and address) to fnd the correct EmpID.

Insert into EmpBasic 
    Select T1Name, T1Address from EmpFull_Temp 
        Where ObjectID < 106 

Insert into EmpProject 
    Select A.EmpID, B.T1EmpProject from EmpBasic as A, EmpFull_Temp as B 
        Where A.Name = B.Name And A.Address = B.Address

If the answer is no then there is no easy solution I know of - in SQL Server 2005 (I've no idea if this is any different in 2008), it's not possible to OUTPUT values that are not inserted. I've got around this issue in the past by using one of the other fields (e.g. Name) to temporarily store the original ID (in this case, ObjectID), use that to join when inserting the child records as described above and then gone back to update the parent records o remove/replace the temporary values. It's not nice but I've not found a better way.

Insert into EmpBasic 
    Select cast(ObjectID as varchar(50)) as name, T1Address from EmpFull_Temp 
        Where ObjectID < 106 

Insert into EmpProject 
    Select A.EmpID, B.T1EmpProject from EmpBasic as A, EmpFull_Temp as B 
        Where A.Name = cast(B.ObjectID as varchar(50))

Update EmpBasic 
    Set Name = B.T1Name
    from EmpBasic as A, EmpFull_Temp as B 
        Where A.Name = cast(B.ObjectID as varchar(50))

Please note: I've not tested the sample SQL given above but I hope it gives you an idea of how you might approach this.

Answer 2

Add an ObjectID column to the EmpBasic table to facilitate the data transfer then drop it when you're done. I'm assuming this is a one-time operation, I don't recommend adding and dropping a column if this is on-going

Answer 3

I have used the Stack Exchange Data Explorer to investigate alternative solutions. The only one with promise at the moment is shown here. It is effectively @ScotHauder's answer, except using a temporary table that has the ObjectID column and using IDENTITY_INSERT to move the generated EmpId values into EmpBasic.

If you have to do this multiple times you need to get the EmpBasic_Temp EmpId IDENTITY starting value to be Max(EmpBasic.EmpID)+1.

Answer 4

With your EmpProject join table, you probably don't want the primary key constraint on only the EmpID column

DECLARE @Count          int
DECLARE @NextEmpID      int
DECLARE @StartObjectID  int
DECLARE @EndObjectID    int

-- range of IDs to transfer (inclusive)
SET @StartObjectID  = 1
SET @EndObjectID    = 105

BEGIN TRAN
    -- lock tables so IDENT_CURRENT is valid
    SELECT @Count = COUNT(*) FROM [EmpBasic] WITH (TABLOCKX, HOLDLOCK)
    SELECT @Count = COUNT(*) FROM [EmpProject] WITH (TABLOCKX, HOLDLOCK)

    SELECT @NextEmpID = IDENT_CURRENT('EmpBasic') 

    SET IDENTITY_INSERT [EmpBasic] ON
    INSERT [EmpBasic] ([EmpID], [Name], [Address])
    SELECT @NextEmpID + ROW_NUMBER() OVER(ORDER BY ObjectID), [T1Name], [T1Address]
    FROM [EmpFull_Temp]
    WHERE [ObjectID] BETWEEN @StartObjectID AND @EndObjectID
    SET IDENTITY_INSERT [EmpBasic] OFF

    INSERT [EmpProject]([EmpID], [EmpProject])
    SELECT @NextEmpID + ROW_NUMBER() OVER(ORDER BY ObjectID), [T1EmpProject]
    FROM [EmpFull_Temp]
    WHERE [ObjectID] BETWEEN @StartObjectID AND @EndObjectID

COMMIT TRAN