CODEWITHSUNDEEP
c#sqlsql-servercopy
Nard Dog
Nard Dog

Reputation: 916

Need SQL help with copying rows

I have a table that has a bunch of columns.

There is one primary key that is an ID as an int data type and no foreign keys. This PK is set with Identity Specification with the increment and seed both set at 1 whatever that means. It is set to Yes for Not For Replication. There are two other rows that are important, the year for the data and a reference number both of which I will be using to specifically select which rows to copy.

Is there any way using 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 ID by itself? The number of rows that will be copied may be different each time so it needs to be flexible. As in the system picks the ID without any more trouble or have it possibly get the max ID, and increment it by one for each row that is copied or whatever. I will also put a new year in the column but that is easy enough compared to the main problem of having it set the primary key ID's by itself for each row through a SQL statement for this type of column...

Please help! I'm not a total SQL newbie but I am still learning and have never come across this type of problem with the specific Not For Replication etc issues before.

Upvotes: 3

Views: 327

Answers (2)

Anastasios Selmani
Anastasios Selmani

Reputation: 3699

If you don't want to add all columns one by one but just exclude the primary Identification column you could use the following.

Let's say the name of your table is entity

-- Copy row without Id
SELECT * INTO TempTable FROM entity WHERE entity_id = 1; // AND OTHER CONDITIONS
ALTER TABLE TempTable DROP COLUMN entity_id;
INSERT INTO entity SELECT * FROM TempTable;
DROP TABLE TempTable;

I use it a lot to copy rows in tables that have a lot of columns.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838186

Since your primary key is an identity column you can select the rows you want (except don't select the identity column) and reinsert them into the same table:

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

Upvotes: 2

Related Questions