Insert numbers in table in sequence but without IDENTITY set

Question

I have few tables, in one of them I am doing load from excel file and then i am doing insert from select In target table i have column id which doesn't have IDENTITY on it and i cannot create it, so I used ROW_NUMBER() in my select for INSERT, however now I am having problem, when next time I'm doing load from file into my temp table from which I am selection data for INSERT row number starts from 1 and if iam trying to do insert i am getting error obout problems with Primary key/ So i need to add ID based on id which is already in target table for example if in target table last record 1000 first record from new Insert has to have 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

any Ideas how to continue insert id in sequence? I CANNOT use IDENTITY on this table. I hope you can understand my explanetions

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

and some tests:

            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