0

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:

  1. 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)

  1. 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.

  • I'm not really following what you want - could you post sample output data, and the changes before and after that you're looking for? – George.Palacios May 15 '18 at 12:40
  • What SELECT * FROM inserted WHERE 1 = 0 returns? – McNets May 15 '18 at 13:00
  • The purpose of the UNION in this statement is to create the new table, including column ID but it will be a simple INT column without it being IDENTITY. Found it here: dba.stackexchange.com/questions/916/… – FaultyOverflow May 15 '18 at 13:07
0

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.

  • I have updated the original question: the name of the column will be unknown because the purpose of this is to have something generic. – FaultyOverflow May 15 '18 at 13:10
  • The only way I know of then uses dynamic SQL and looks into the system catalogs. But I really can't recommend doing that, for one thing you would be forced to use global temp tables which would inhibit concurrent access without lots of precautions. Why do you need it to be marked as a primary key? Just for performance? – Jonathan Fite May 15 '18 at 13:18
  • Also, i'm calling a stored procedure within the trigger, aiming to handle the contents of the "inserted" table. Since the "inserted" table seems to be accessible only from within the trigger, i'm creating a temp table for that purpose. – FaultyOverflow May 15 '18 at 13:19
  • Hmmm, now that was a good question. The stored proc is trying to retrieve the primary key from INFORMATION_SCHEMA for the inserted table in order to use it in some dynamically created statements. There's no need for that though because the structure of the "inserted" table is exactly the same as the original table, so it can be retrieved from there. This makes my question rather pointless. – FaultyOverflow May 15 '18 at 13:25
0

As far as I can understand, you want to create a primary key constraint by executing the SELECT * INTO statement. Actually is not possible create the constraint on the way do you want. You need to populate the table, and then run a ALTER TABLE #ins ADD PRIMARY KEY (ID) statement; it's not possible achieve the desired result in a single statement.

However, someone has asked this kind of question on the MSDN forum; click here for a detailed explanation.

  • Sorry, I have updated the original question: the name of the column will be unknown because the purpose of this is to have something generic. – FaultyOverflow May 15 '18 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.