Need SQL help when copying rows

Question

I have a table with a bunch of columns.

There is one primary key, which is an ID as an int data type and has no foreign keys. This PK is specified using the identity specification with an increment and a seed set to 1, which means. The No Replication option is set to Yes. There are two other lines that are important, a year for the data and a reference number, both of which I will use to specifically select which lines to copy.

Is there a way to use SQL or a combination of SQL and C # to copy a specific set of rows by reference number and assign each row a unique primary key identifier myself? The number of lines to be copied can be different each time, so it should be flexible. As in the system, you select the identifier without any problems or you can get the maximum identifier and increase it by one for each copied row or any other. I will also add a new year to the column, but this is quite simple compared to the main problem, that he himself set the primary key identifier for each row through the SQL statement for this type of column ...

Please, help! I am not a complete newbie to SQL, but I am still involved and have never encountered this type of problem with specific Not For Replication problems, etc.

Answer 1

Since your primary key is an identification column, you can select the rows you need (except the identification column) and insert them into the same table:

INSERT INTO table1 (col1, col2, col3)
SELECT (col1, col2, col3) FROM table1
WHERE ....