Identity Increment

Question

I am combining 2 tables into 1, by using the following statements

Select Calls.*,Patient.*
INTO Calls2
From Calls,Patient

Now, i set one of the column as primary key but I cant get that primary key to have Identity Increment. What should i do to do this?

Thanks in advance, Madhu

It is not "denormalization" at all. It is just SQL statement. Have a read here http://msdn.microsoft.com/en-us/library/ms190750.aspx and here http://msdn.microsoft.com/en-us/library/ms188029.aspx — NoWar, Feb 21 '12 at 16:38
This is a cross join so unless one of the tables only has `<= 1` row there will be duplicate values for every column. How are you creating a PK from this? — Martin Smith, Feb 21 '12 at 16:40
@Martin the identity will be the PK, of course. Why would we care if the actual data is unique? :-) — Aaron Bertrand, Feb 21 '12 at 21:25

score 1 · Answer 1 · answered Feb 21 '12 at 16:36

Perform a CREATE TABLE the "hard" way? You can easily build such a command from sys.columns.

Or set the property in Management Studio. Just be aware that this drops the table and re-creates it, so if the table is large, expect your application to lock up for a while.

score 1 · Accepted Answer · answered Feb 21 '12 at 16:39

1

First, you should know that you are doing a cartesian product of your tables. If you want to add an identity column when you are creating your table, then do the following:

Select IDENTITY(INT,1,1) ID, Calls.*,Patient.*
INTO Calls2
From Calls,Patient

answered Feb 21 '12 at 16:39

Lamak

69,480
12
108
116

score 0 · Answer 3 · answered Feb 21 '12 at 16:35

Using SQL Server Management Studio

Right mouse click the table and click Design. In the Column Properties at the bottom of the right hand window, scroll down to Identity Specification. Expand this and then change the (Is Identity) value to Yes.

However, if you already have an Id column on the table, from either the Calls or Patient table, this may not work. I'd suggest creating a new Id column and configuring the Id column as above.

eric · Answer 4 · 2012-02-21T16:56:35.140

0

You can't turn an existing column into an identity column.* If you need an identity column, add a new column:

ALTER TABLE Calls2 ADD Calls2Id int NOT NULL IDENTITY(1,1) ;

Or, if you need to use one of the existing columns, you'll need to use an explicit CREATE TABLE to define the table rather than SELECT...INTO. You'll also need to use SET IDENTITY INSERT Calls2 ON before your INSERT INTO.

CREATE TABLE Calls2 (
    ...columns...
) ;
SET IDENTITY_INSERT Calls2 ON ;
INSERT INTO Calls2 (... columns ...) SELECT ... columns ... FROM Calls, Patient ;
SET IDENTITY_INSERT Calls2 OFF ;

*Although the designer may pretend to turn it into an identity column, that's not actually what's happening. As Aaron Bertrand mentions in his response, the table is being recreated with the column defined as an identity column to begin with.

edited Feb 21 '12 at 16:56

answered Feb 21 '12 at 16:41

eric

401
3
7

[Yes you can...](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661) – Martin Smith Feb 21 '12 at 16:42
I forgot that the designer lets you do that. Added a note to clarify (I hope). Thanks. – eric Feb 21 '12 at 17:00
That's not what I meant because the whole table gets re-created. You can do it as a metadata only change with `ALTER TABLE ... SWITCH` – Martin Smith Feb 21 '12 at 17:02

Identity Increment

4 Answers4