在保持 ID 的同时在表中插入行 [英] Insert rows in table while maintaining IDs

问题描述

<预><代码>表MasterCategoryID MasterCategoryDe​​sc1 住房1 住房1 住房2 车2 车2 车3 商店表B身份证件说明1 家2 家3 平面4 车插入表A([主类别ID][MasterCategoryDe​​sc])选择如果(描述)不在(从表A中选择MasterCategoryDe​​sc)然后(从表A中选择max(MasterCategoryID)+1)否则(从 TableA 中选择前 1 个 MasterCategoryID，其中 MasterCategoryDe​​sc = Description)以 [MasterCategoryID] 结尾,描述为 MasterCategoryDe​​sc来自表B

我想使用 SQL/存储过程从 tableB 到 tableA 输入行.例如，当插入第一行Home"时，它在 MastercategoryDe​​sc 中不存在，因此将在 MasterCategoryID 中插入4".第二行应在 MasterCategoryID 中再次保留4".下面的代码在第一行之后执行此操作，所有行的 MastercategoryID 保持不变.我不知道如何在插入新行时跟踪 ID.

附言请不要回复说我需要使用 IDENTITY() 索引.我必须保持表结构不变，不能改变它.谢谢

解决方案

使用 CURSOR 来完成工作.游标循环遍历 TableA 的每一行，如果在 TableB 中找不到 MasterCategoryID，它就会增加.这发生在 TableA 的下一行加载到游标之前......

DECLARE @ID int声明 @Description VARCHAR(MAX)声明 my_cursor CURSOR FORSELECT ID，来自表B的描述打开 my_cursor从 my_cursor 获取下一个INTO @ID, @Description而@@FETCH_STATUS = 0开始插入表A(MasterCategoryID，MasterCategoryDe​​sc)SELECT CASE WHEN @Description NOT IN (SELECT MasterCategoryDe​​sc FROM TableA)然后 (SELECT MAX(MasterCategoryID)+1 FROM TableA)ELSE(选择前 1 个 MasterCategoryID从表AWHERE MasterCategoryDe​​sc = @Description)END AS MasterCategoryID，描述为 MasterCategoryDe​​sc从表B哪里 ID = @ID从 my_cursor 获取下一个INTO @ID, @Description结尾

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

解决方案

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

这篇关于在保持 ID 的同时在表中插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！