CODEWITHSUNDEEP
sql
Kate
Kate

Reputation: 372

Copy row to same table with different ID

I have table Match, with this columns: ID(with Identity spec.),Team1,Team2. I need copy the row with ID=1 with all columns to NEW row with new automatic ID.

Now I have this code:

SET IDENTITY_INSERT Match ON
INSERT INTO Match (ID,Team1,Team2)
SELECT
  ???,Team1,Team2
FROM
  Match
WHERE ID='1';

But I don't know, how do I get a new automatic ID?

Upvotes: 1

Views: 2290

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

By not turning on IDENTITY_INSERT and letting the system create the ID:

INSERT INTO Match (Team1,Team2)
SELECT
  Team1,Team2
FROM
  Match
WHERE ID=1;

When IDENTITY_INSERT is turned on, you're telling the system "I'm going to assign a value where normally you would auto-generate one", and so you would have to provide an explicit value. But since the default behaviour of the system is to auto-generate one, and what you want is the auto-generated value, you shouldn't turn this option on.

There's no facility to ask the system "please give me the next auto-generated value you would have assigned" (SQL Server 2012 has Sequences, which are similar in concept to IDENTITY columns and do support the ability to ask for values, but they two systems aren't the same)

Upvotes: 2

DevZer0
DevZer0

Reputation: 13545

If your ID column is auto-increment then do 

INSERT INTO Match (Team1,Team2)
SELECT
  Team1,Team2
FROM
  Match
WHERE ID='1';

Upvotes: 3

Sklivvz
Sklivvz

Reputation: 31183

You simply don't pass it!

INSERT INTO Match (Team1,Team2)
SELECT
  Team1,Team2
FROM
  Match
WHERE ID='1';

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

Here is snippet.

INSERT INTO Match (Team1,Team2)
SELECT
  Team1,Team2
FROM
  Match
WHERE ID='1';

Upvotes: 2

Related Questions