TSQL 插入记录和轨道 ID [英] TSQL Inserting records and track ID

问题描述

我想在下面的表格中插入记录(带有示例数据的表格结构).我必须使用 TSQL 来实现这一点:

I would like to insert records in a table below (structure of table with example data). I have to use TSQL to achieve this:

MasterCategoryID    MasterCategoryDesc  SubCategoryDesc     SubCategoryID
1                   Housing             Elderly              4 
1                   Housing             Adult                5  
1                   Housing             Child                6
2                   Car                 Engine               7
2                   Car                 Engine               7
2                   Car                 Window               8
3                   Shop                owner                9   

例如，如果我输入一个带有 MasterCategoryDe​​sc = 'Town' 的新记录，它将在 MasterCategoryID 中插入 '4' 和相应的 SubCategoryDe​​sc + ID.

So for example if I enter in a new record with MasterCategoryDesc = 'Town' it will insert '4' in MasterCategoryID with the respective SubCategoryDesc + ID.

我可以通过删除 SubCategoryDe​​sc 和 SubCategoryID 列来简化这个问题吗?我现在如何仅使用 2 列 MasterCategoryID 和 MasterCategoryDe​​sc 来实现此目的

CAN I SIMPLIFY THIS QUESTION BY REMOVING THE SubCategoryDesc and SubCategoryID columns. How can I achieve this now just with the 2 columns MasterCategoryID and MasterCategoryDesc

推荐答案

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

SQL FIDDLE

如果你愿意，我也可以创建一个 SP.但是你说你想要一个 T-SQL

If you want i can create a SP too. But you said you want an T-SQL

这篇关于TSQL 插入记录和轨道 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！