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

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.

CodePudding user response：

Table2.Number should be IDENTITY. You can then just insert the names from table 1 in table 2.

create table table2(number int not null identity(1,1), name char(1));
insert into table2 values ('A');
insert into table2 select name from table1;

Fiddle

CodePudding user response：

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
;

CodePudding user response：

You'll want to use INSERT INTO SELECT syntax like below to achieve this.

INSERT INTO table2 
SELECT MAX(Number)   1, 
    Name 
FROM Table1