如何将一组键 (UniqueID) 添加到临时表以稍后插入生产表 [英] How to Add a Set of Keys (UniqueIDs) to a Temp table to later INSERT into Production Table

问题描述

我已经准备好将数据插入到我的生产表中，但是 ID 列是 NULL，需要在插入之前预先填充 ID.我在另一个临时表中有这些 ID...我想要的只是将这些 ID 应用到我的临时表中的记录.

I have the data ready to Insert into my Production table however the ID column is NULL and that needs to be pre-populated with the IDs prior to Insert. I have these IDs in another Temp Table... all I want is to simply apply these IDs to the records in my Temp Table.

例如...假设我有 10 条记录，所有记录都只需要 ID.我在另一个临时表中有 10 个 ID……它们只需要应用于我的准备插入"临时表中的 10 条记录.

For example... Say I have 10 records all simply needing IDs. I have in another temp table exactly 10 IDs... they simply need to be applied to my 10 records in my 'Ready to INSERT' Temp Table.

我在 Oracle 工作了大约 9 年，我可以通过使用 FORALL 循环遍历我的集合"来完成此操作...基本上我只需遍历我的准备插入"临时表和每一行应用我的其他集合"中的 ID...在 SQL Server 中我正在使用临时表而不是集合，嗯...除了 WHILE 之外，SQL Server 中没有 FORALL 循环或任何花哨的循环.

I worked in Oracle for about 9 years and I would have done this simply by looping over my 'Collection' using a FORALL Loop... basically I would simply loop over my 'Ready to INSERT' temp table and for each row apply the ID from my other 'Collection'... in SQL Server I'm working with Temp Tables NOT Collections and well... there's no FORALL Loop or really any fancy loops in SQL Server other than WHILE.

我的目标是了解在 SQL Server 中完成此操作的适当方法.我了解到，在 SQL Server 世界中，许多 DML 操作都是基于 SET 的，而当我在 oracle 中工作时，我们通过数组/集合处理数据，并使用 CURSORS 或 LOOP 来简单地遍历数据.我在 SQL Server 世界中看到过使用 CURSORS 和/或逐条记录遍历数据是令人不悦的.

My goal is to know the appropriate method to accomplish this in SQL Server. I have learned that in the SQL Server world so many of the DML operations are all SET Based whereas when I worked in oracle we handled data via arrays/collections and using CURSORS or LOOPs we would simply iterate thru the data. I've seen in the SQL Server world using CURSORS and/or iterating thru data record by record is frowned upon.

帮助我从长期存在的Oracle"空间中解放出来，进入我需要进入的SQL Server"空间.这有点困难.

Help me get my head out of the 'Oracle' space I was in for so long and into the 'SQL Server' space I need to be in. This has been a slight struggle.

下面的代码是我目前如何实现的，但它看起来很复杂.

The code below is how I've currently implemented this however it just seems convoluted.

    SET NOCOUNT ON;

    DECLARE @KeyValueNewMAX INT,
            @KeyValueINuse  INT,
            @ClientID       INT,
            @Count          INT;

    DROP TABLE IF EXISTS #InterOtherSourceData;
    DROP TABLE IF EXISTS #InterOtherActual;
    DROP TABLE IF EXISTS #InterOtherIDs;

    CREATE TABLE #InterOtherSourceData      -- Data stored here for DML until data is ready for INSERT 
    (
        UniqueID            INT IDENTITY( 1, 1 ),
        NewIntOtherID       INT,
        ClientID            INT
    );

    CREATE TABLE #InterOtherActual          -- Prod Table where the data will be INSERTED Into
    (
        IntOtherID          INT,
        ClientID            INT
    );

    CREATE TABLE #InterOtherIDs             -- Store IDs needing to be applied to Data
    (
        UniqueID            INT IDENTITY( 1, 1 ),
        NewIntOtherID       INT
    );

    BEGIN

        /* TEST Create Fake Data and store it in temp table */
            WITH fakeIntOtherRecs AS
            (
                SELECT 1001 AS ClientID, 'Jake'  AS fName, 'Jilly'   AS lName UNION ALL
                SELECT 2002 AS ClientID, 'Jason' AS fName, 'Bateman' AS lName UNION ALL
                SELECT 3003 AS ClientID, 'Brain' AS fName, 'Man'     AS lName 
            )

            INSERT INTO #InterOtherSourceData (ClientID)
            SELECT fc.ClientID--, fc.fName, fc.lName
            FROM fakeIntOtherRecs fc
            ;
        /* END TEST Prep Fake Data */


        /* Obtain count so we know how many IDs we need to create */
        SELECT @Count = COUNT(*) FROM #InterOtherSourceData;

        PRINT 'Count: ' + CAST(@Count AS VARCHAR);

        /* For testing set value OF KeyValuePre to the max key currently in use by Table */
        SELECT @KeyValueINuse = 13;

        /* Using the @Count let's obtain the new MAX ID... basically Existing_Key + SourceRecordCount = New_MaxKey */
        SELECT @KeyValueNewMAX = @KeyValueINuse + @Count /* STORE new MAX ID in variable */

        /* Print both keys for testing purposes to review */
        PRINT 'KeyValue Current: ' + CAST(@KeyValueINuse AS VARCHAR) + ' KeyValue Max: ' + CAST(@KeyValueNewMAX AS VARCHAR);

        /* Using recursive CTE generate a fake table containing all of the IDs we want to INSERT into Prod Table */
        WITH CTE AS
        (
            SELECT (@KeyValueNewMAX - @Count) + 1 AS STARTMINID, @KeyValueNewMAX AS ENDMAXID  UNION ALL

            /* SELECT FROM CTE to create Recursion */
            SELECT STARTMINID + 1  AS STARTMINID, ENDMAXID FROM CTE 
            WHERE (STARTMINID + 1) < (@KeyValueNewMAX + 1)
        )




        INSERT INTO #InterOtherIDs (NewIntOtherID)
        SELECT c.STARTMINID AS NewIntOtherID
        FROM CTE c
        ;



        /* Apply New IDs : Using the IDENTITY fields on both Temp Tables I can JOIN the tables by the IDENTITY columns
            | Is there a BETTER Way to do this?... like LOOP over each record rather than having to build up common IDs in both tables using IDENTITY columns?
        */
        UPDATE #InterOtherSourceData SET NewIntOtherID = oi.NewIntOtherID
        FROM #InterOtherIDs oi
            JOIN #InterOtherSourceData o ON o.UniqueID = oi.UniqueID
        ;

        /* View data that is ready for insert */
        --SELECT *
        --FROM #InterOtherSourceData
        --;

        /* INSERT DATA INTO PRODUCTION TABLE */
        INSERT INTO #InterOtherActual (IntOtherID, ClientId)
        SELECT NewIntOtherID, ClientID
        FROM #InterOtherSourceData
        ;

        SELECT * FROM #InterOtherActual;
    END

推荐答案

要在 SQL Server 中预先生成键值，请使用 sequence 而不是 IDENTITY 列.

To pre-generate key values in SQL Server use a sequence rather than an IDENTITY column.

例如

drop table if exists t
drop table if exists #t_stg 

drop sequence t_seq

go
create sequence t_seq start with 1 increment by 1

create table t(id int primary key default (next value for t_seq),a int, b int)

create table #t_stg(id int, a int, b int)

insert into #t_stg(a,b) values (1,2),(3,3),(4,5)

update #t_stg set id = next value for t_seq

--select * from #t_stg

insert into t(id,a,b) 
select * from #t_stg 

这篇关于如何将一组键 (UniqueID) 添加到临时表以稍后插入生产表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！