In a trigger, I have created a table using SELECT * INTO. I have done that in a specific way, in order to create the table without also creating the identity column that exists in it (let's name this column ID):
SELECT * INTO #ins FROM (SELECT * FROM inserted UNION ALL SELECT * FROM inserted WHERE 1 = 0) AS a
The problem is that the new table (#ins) does contain the identity column (which I want it to) but it has not marked is as Primary Key (which is how it was in the original table, and I would like to keep it that way).
I would like to know the easier of the following two:
- How could I "read" the primary key from the original table and alter the new one to also add it there? I would like this to be generic, so assume the name of the ID column will be unknown.
or (if easier)
- Is there another way to create table #ins from the original, retaining the column "ID" and its definition as primary key, but without it being an IDENTITY one?
EDIT: OK, as stated in a comment below, the task i was trying to achieve can be done in another way, using the primary key from the original table. This makes the question above rather pointless.