Sql-server – SQL Server – Copying a key from one table to another

identityprimary-keysql server

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.

Best Answer

For permanent reused code like this, you really should be explicitly defining your temporary table definitions. If you are looking for an easy way to do that, please see below. I'll also include information on your actual question.

Easy way to generate table definition:

locate the object in object explorer
right click on it and select "script --> create table --> to SSMS window"

This should generate a new SSMS window with the table you selected scripted there. Depending on your options, you will have indexes as well at the bottom.

To make it a temporary table, just change the name of the table.

Remove IDENTITY

Locate the identity column and remove the identity definition more than likely "IDENTITY(1,1)".

Example:

CREATE TABLE #INS
(
MyIdentityID INT NOT NULL PRIMARY KEY
, MyValue VARCHAR(100) NOT NULL
)

Please note, this is a simple and straightforward method that directly answers your question. Any use of temporary tables in triggers makes me suspicious and I would like to know the full business reason behind it; it's possible that a better solution exists.

Related Solutions

Sql-server – Primary Key choice on table with unique identifier

There are a few things to consider here:

How is data most often looked up in this table?
How is data most often sorted in this table?
Does this table relate to others as the parent record (i.e. will other tables FK to the PK of this table)?

Also, please keep in mind:

The key fields of a Clustered Index are copied into the non-clustered indexes on the same table
For Clustered Indexes that are not a Primary Key (implied unique) or at least declared as UNIQUE, a hidden "uniqueifier" field is added to rows that would otherwise be duplicates.
NEWSEQUENTIALID() is sequential per each restart of the SQL Server service. It is possible that the starting value after a restart is less than the previous lowest value.

Hence:

If the PK field of this table shows up as a FK field in other tables, there is a definite performance implication of choosing to use a UNIQUEIDENTIFIER instead of an INT as the FKed tables would then have a larger FK field in them.
How many rows do you really expect to have in this table? INT is 4 bytes (compared to the 8 bytes of a BIGINT) and has a max value of 2,147,483,647. If you might have slightly over 2.14 billion items, you can also start the IDENTITY range at the min value of each datatype, which for INT is -2,147,483,648. Starting at the low-end gives you the full 4.294 billion values to use. Compared to the 8 bytes of the DATETIME field if going with the Created field, plus add in the size of another field to make it unique, or the uniqueifier for any duplicate rows.
Since the key field(s) of the Clustered Index are included in Nonclustered indexes, that increases the chances of having a covering index without needing to INCLUDE other columns. Meaning, if you have the Clustered Index on the INT PK and a Nonclustered index on the UNIQUEIDENTIFIER, then JOINing to another table on that INT PK field while specifying the GUID value in a WHERE clause (assuming no other fields from this table are in the query) won't have to go back to the table since the Nonclustered Index will have both of the requiered fields in it. Does the Created field give the same benefit? Likely not.

IF no other tables ever JOIN to this table:

THEN it might be ok to use the Created field as the non-unique Clustered Index and the Id field as the Nonclustered PK.
ELSE it is typically best to add an INT (unless you need more than 4.294 billion values) IDENTITY field, AssetId, as the Clustered PK, and the Id UNIQUEIDENTIFIER field as a Nonclustered Index. Since you likely already have code referencing the UNIQUEIDENTIFIER field as Id, I wouldn't change that name.

Sql-server – Why would a table use its primary key as a foreign key to itself

Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key. A good example is a table of employees:

EmployeeId    Int     Primary Key
EmployeeName  String
ManagerId     Int     Foreign key going back to the EmployeeId

So in this case there is a foreign key from the table back to itself. All managers are also employees so the ManagerId is actually the EmployeeId of the manager.

Now on the other hand if you mean someone used the EmployeeId as the foreign key back to the Employee table then it was probably a mistake. I did run a test and it's possible but it wouldn't have any real use.

CREATE TABLE Employee (EmployeeId Int PRIMARY KEY,
                        EmployeeName varchar(50),
                        ManagerId Int);


ALTER TABLE Employee ADD CONSTRAINT fk_employee 
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId);

Best Answer

Related Solutions

Sql-server – Primary Key choice on table with unique identifier

Sql-server – Why would a table use its primary key as a foreign key to itself

Related Question