How can I insert into from one table to another with autoincrement in SQL Server

If for example I have these 2 tables in SQL Server:

            Table 1       ||          Table 2
--------------------------||----------------------------
    Number    |    Name   ||     Number    |    Name
--------------|-----------|| --------------|------------
      1       |     B     ||       1       |      A   
      2       |     C     ||               |    

What I am trying to do is to insert the entries from table 1 to table 2, but I want table 2 to autoincrement the Number. So I want it to become like this :

           Table 2        ||
--------------------------||
    Number    |    Name   ||
--------------|-----------||
      1       |     A     ||
      2       |     B     ||
      3       |     C     ||

I tried queries like this but it didn’t work:

Insert into table2 (Number, Name)
    select 
        (select max(number) + 1 from table1), Name 
    from table1

Maybe you will suggest to make the number in table2 primary key, but I want to do it using the max number like the query above.

Thanks in advance.

Answer

This might be a bit too simplistic, and maybe you’re looking for something more sophisticated, but…

INSERT INTO Table2 (Number, Name)
SELECT
   T.Number + X.MaxRowNumber
  ,T.Name
FROM Table1 T
INNER JOIN (SELECT MAX(Number) AS MaxRowNumber FROM Table2) X
  ON 1 = 1
;

This approach is based on your statement:

Maybe you will suggest to make the number in table2 primary key, but I want to do it using the max number like the query above.

I understood from this that you don’t want to set Table2.Number as an IDENTITY column, but rather to perform the autoincrement during the INSERT operation.

I tend to agree with the other answers saying that Table2.Number should be an IDENTITY column and thus auto-increment itself without the need to calculate it, but if that is not what you want, then this answer should help you.

You can test this using table variables with real data to make sure it’s what you’re looking for:

DECLARE @TABLE1 AS TABLE (Number INT, Name CHAR(1));
DECLARE @TABLE2 AS TABLE (Number INT, Name CHAR(1));
INSERT INTO @TABLE1 (Number, Name) VALUES ('1','B'), ('2','C');
INSERT INTO @TABLE2 (Number, Name) VALUES ('1','A');

INSERT INTO @TABLE2 (Number, Name)
SELECT
   T.Number + X.MaxRowNumber
  ,T.Name
FROM @TABLE1 T
CROSS APPLY (SELECT MAX(Number) AS MaxRowNumber FROM @TABLE2) X
;

SELECT * FROM @TABLE2
;