sql-server - 按顺序在表中插入数字但未设置 IDENTITY

Question

我有几个表，在其中一个表中，我正在从 excel 文件加载，然后我正在从选择中插入在目标表中，我有 id 列，上面没有 IDENTITY 并且我无法创建它，所以我在我的中使用了 ROW_NUMBER()选择 INSERT，但是现在我遇到了问题，下次我从文件加载到我的临时表时，我从中选择数据 INSERT 行号从 1 开始，如果我试图插入我是出现主键问题的错误/所以我需要根据目标表中已经存在的 ID 添加 ID例如，如果在目标表中最后一条记录 1000来自新插入的第一条记录必须有 id 1001

    INSERT  INTO table1
                    ( Id ,
                      OkeiId ,
                      OkpdId ,
                      OkvedId ,
                   )
                    SELECT 
                    --  (SELECT MAX (id) FROM table1) AS 'last id in table',
                    ROW_NUMBER() OVER ( ORDER BY ( SELECT 0 ) ) AS Row , -- Id - int
                            a.[Id] AS OkeId ,  - int
                            c.[Id] AS OkpId , - int
                            D.[Id] AS OkvId ,  - int

                    FROM    [dbo].#table b
                            LEFT OUTER JOIN table2 a ON b.F6 = a.[NationalSymbol]
                            LEFT OUTER JOIN table3 c ON b.F4 = c.Code
                            LEFT OUTER JOIN table4 D ON b.F5 = D.Code
                            LEFT OUTER JOIN table1 f ON b.f1 = f.Code
                    WHERE   f.code IS NULL

任何想法如何继续按顺序插入 id？我不能在此表上使用 IDENTITY。我希望你能理解我的解释

Answer 1

INSERT  INTO table1
                ( Id ,
                  OkeiId ,
                  OkpdId ,
                  OkvedId 
               )
                SELECT 
                --  (SELECT MAX (id) FROM table1) AS 'last id in table',
                a.last_id + ROW_NUMBER() OVER ( ORDER BY ( SELECT 0 ) ) AS Row , -- Id - int
                        a.[Id] AS OkeiId , -- OkeiId - int
                        c.[Id] AS OkpdId , -- OkpdId - int
                        D.[Id] AS OkvedId  -- OkvedId - int

                FROM    [dbo].#table b
                        LEFT OUTER JOIN table2 a ON b.F6 = a.[NationalSymbol]
                        LEFT OUTER JOIN table3 c ON b.F4 = c.Code
                        LEFT OUTER JOIN table4 D ON b.F5 = D.Code
                        LEFT OUTER JOIN table1 f ON b.f1 = f.Code
                        CROSS JOIN (SELECT MAX (id) AS last_id FROM table1 ) a
                WHERE   f.code IS NULL

和一些测试:

            CREATE TABLE #a
            (
                a INT PRIMARY KEY,
                aa int
            )

            CREATE TABLE #b
            (
                b int
            )

            INSERT INTO #a VALUES(1,1);

            INSERT INTO #b VALUES(1);
            INSERT INTO #b VALUES(2);
            INSERT INTO #b VALUES(3);
            INSERT INTO #b VALUES(4);
            INSERT INTO #b VALUES(5);

            INSERT INTO #a

            SELECT last_id + ROW_NUMBER() OVER(ORDER BY b.b), b.b FROM #b b CROSS JOIN (SELECT MAX(a) last_id FROM #a) a