Insert rows in table while maintaining IDs

问题:

TABLEA
MasterCategoryID    MasterCategoryDesc
1                   Housing
1                   Housing 
1                   Housing 
2                   Car
2                   Car
2                   Car
3                   Shop   

TABLEB
ID                  Description
1                   Home
2                   Home
3                   Plane
4                   Car

INSERT into TableA
(
    [MasterCategoryID]
    [MasterCategoryDesc]
)
Select
     case when (Description) not in (select MasterCategoryDesc from TableA) 
        then (select max(MasterCategoryID)+1 from TableA)
        else (select top 1 MasterCategoryID from TableA where MasterCategoryDesc = Description)
     end as [MasterCategoryID]

    ,Description as MasterCategoryDesc
from TableB

I want to enter rows using SQL/Stored Procedure from tableB to tableA. for example when inserting first row 'Home' it does not exist in MastercategoryDesc therefore will insert '4' in MasterCategoryID. Second row should keep the '4' again in MasterCategoryID. The code below does it however after the first row the MastercategoryID remains the same for all rows. I Dont know how to keep track of ids while inserting the new rows.

p.s. Pls do not reply by saying i need to use IDENTITY() index. I have to keep the table structure the same and cannot change it. thanks

回答1:

Use a CURSOR to do the work. The cursor loops through each row of TableA and the MasterCategoryID increases if it is not found in TableB. This happens before the next row of TableA is loaded into the cursor ...

DECLARE @ID int
DECLARE @Description VARCHAR(MAX)

DECLARE my_cursor CURSOR FOR 
  SELECT ID, Description FROM TableB
OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @ID, @Description

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT into TableA(MasterCategoryID, MasterCategoryDesc)
    SELECT CASE WHEN @Description NOT IN (SELECT MasterCategoryDesc FROM TableA) 
           THEN (SELECT MAX(MasterCategoryID)+1 FROM TableA)
           ELSE (SELECT TOP 1 MasterCategoryID 
                 FROM TableA 
                 WHERE MasterCategoryDesc = @Description)
           END AS MasterCategoryID, Description as MasterCategoryDesc
    FROM TableB
    WHERE ID = @ID

    FETCH NEXT FROM my_cursor
    INTO @ID, @Description
END

回答2:

Create a new table your_table with fields x_MasterCategoryDesc ,x_SubCategoryDesc

Insert all your values in that table and the run the below SP.

CREATE PROCEDURE x_experiment
AS
BEGIN

    IF object_id('TEMPDB..#TABLES') IS NOT NULL
    BEGIN
    DROP TABLE #TABLES
    END

    DECLARE @ROWCOUNT INT
    DECLARE @ROWINDEX INT =0,
    @MasterCategoryDesc VARCHAR(256),            
    @SubCategoryDesc VARCHAR(256)

    select IDENTITY(int,1,1) as ROWID,* 
    into #TABLES 
    From your_table

    SELECT @ROWCOUNT=COUNT(*) from #TABLES --where ROWID between 51 and 100

    WHILE (@ROWINDEX<@ROWCOUNT)
    BEGIN
        set @ROWINDEX=@ROWINDEX+1

        Select 
                @MasterCategoryDesc=x_MasterCategoryDesc,
                @SubCategoryDesc=x_SubCategoryDesc
        from #TABLES t
        where rowid = @ROWINDEX

        INSERT into Table1
            ([MasterCategoryID], [MasterCategoryDesc], [SubCategoryDesc], [SubCategoryID])
        select TOP 1
            case when @MasterCategoryDesc not in (select [MasterCategoryDesc] from Table1) 
                then (select max([MasterCategoryID])+1 from Table1)
                else (select distinct max([MasterCategoryID]) from Table1 
                    where [MasterCategoryDesc]=@MasterCategoryDesc
                    group by [MasterCategoryID]) 
            end as [MasterCategoryID]
            ,@MasterCategoryDesc as [MasterCategoryDesc]
            ,@SubCategoryDesc as [SubCategoryDesc]
            ,case when @SubCategoryDesc not in (select [SubCategoryDesc] from Table1) 
                then (select max([SubCategoryID])+1 from Table1 )
                else (select max([SubCategoryID]) from Table1 
                    where [SubCategoryDesc]=@SubCategoryDesc
                    group by [SubCategoryID]) 
            end as [SubCategoryID]

            from Table1
        END
        select * from Table1 order by MasterCategoryID

END
GO

exec x_experiment --SP Execute

SQL FIDDLE

回答3:

Your data structure leaves something to be desired. You shouldn't have a master id column that has repeated values.

But you can still do what you want:

INSERT into TableA ([MasterCategoryID], [MasterCategoryDesc])
Select coalesce(a.MasterCategoryId,
                amax.maxid + row_number() over (partition by (a.MasterCategoryId) order by b.id)
               ),
       coalesce(a.MasterCategoryDesc, b.desc)
from TableB b left outer join
     (select desc, max(MasterCaegoryId) as maxid
      from TableA a
      group by desc
     ) a
     on b.desc = a.desc  left outer join
     (select max(MasterCategoryID) as maxid
      from TableA
     ) amax

The idea is to take the information from the master table when it is available. When not available, then MasterCategoryId will be NULL. A new id is calculated, using row_number() to generate sequential numbers. These are then added to the previous maximum id.