在表中插入行同时维持标识(Insert rows in table while maintainin

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

我想进入使用SQL /存储过程从tableB的到TableA的行。 用于插入第一行“首页”当例如它不存在MastercategoryDe​​sc因此将在MasterCategoryID插入“4”。 第二行应在MasterCategoryID再次保持“4”。 下面的代码做它，然而前行后MastercategoryID仍然是所有行相同。 我不知道如何保持IDS的轨道，同时插入新行。

PS请不要说我需要使用IDENTITY（）指数答复。 我要保持表的结构相同，不能改变它。 谢谢

Answer 1:

使用游标做的工作。 光标遍历表A中的每一行，如果它不是在表B中发现的MasterCategoryID增加。 出现这种情况TableA的下一行被加载到光标之前...

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

Answer 2:

创建字段的新表your_table x_MasterCategoryDesc ， x_SubCategoryDesc

插入该表和运行下面的所有值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

Answer 3:

你的数据结构极不想要的东西。 你不应该有一个具有重复值的主ID列。

但你仍然可以做你想做的：

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

我们的想法是把从主表中的信息可用时。 如果没有，那么MasterCategoryId将是NULL 。 一个新的ID进行计算，使用row_number()来生成序列号。 然后，这些加入到先前的最大ID。

文章来源: Insert rows in table while maintaining IDs