Create a "INTO" table with primary key

Question

Maybe for this community my problem is easy, but for me (a simple Java programmer) it's a BIG problem.

I have a Big DB with more and more data. So, the external db admin had create a job that show me in a temporary table the data that I need. But he had create the table without a primary key and when with my java project go to read this table, I obtain an error.

I can't read this table because the primary key don't exist.

Can I insert in the procedure the possibility to create an autoincremental primary key without changing the structure of this complex procedure?

This is the begin of the stored procedure code:

USE [MYDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[spSchedula_Scadenzario]
as
begin

    drop table MYDB.dbo.tmpTable


 select 
  aa.*
    into MYDB.dbo.tmpTable 
from (...)

Thanks in advance

Answer 1

Sounds like you are looking for the IDENTITY() function:

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

USE [MYDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[spSchedula_Scadenzario]
as
begin

    drop table MYDB.dbo.tmpTable


     select 
    -- Create new identity here.
    NewPrimaryKey = IDENTITY(int, 1, 1),
    aa.*
    into MYDB.dbo.tmpTable 
from (...)

Answer 2

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are:

1. Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be the Primary Key. For example:

   CREATE TABLE dbo.tmpTable
   (
     tmpTableID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
     ...
     {all columns represented by aa.* in the sample query in the Question}
   );
   

2. If you can't change how / when / where the table is being created, you can always add a Column later, and when doing so, you are allowed to specify both that it be an IDENTITY column and have the Primary Key created on it. For example:

   ALTER TABLE [dbo].[tmpTable]
     ADD [tmpTableID] INT NOT NULL
     IDENTITY(1, 1)
     PRIMARY KEY;
   

   This even works if the Table already has data in it: the new IDENTITY column will be populated as expected, starting with the value specified for the seed parameter. However, there is no way to control the order in which the values are assigned (which is one of several reasons to go with option #1, if at all possible).

Additional notes:

• Are you sure that you need a Primary Key and not simply an auto-incrementing / unique column? While it is typically a good idea to have a Primary Key, it is neither required nor the same thing as an auto-incrementing column. I only ask because both the title and text of this Question state that you need a Primary Key, yet you are saying in a comment on the Answer that you accepted that simply having the auto-increment column worked.

• The table you are using is not actually a temporary table. Real Temporary Tables have names starting with #, or ## for Global Temporary Tables. The table you are using dbo.tmpTable is just a regular, permanent table that is prefixed with "tmp" to indicate that it is probably just for this process and not part of the data model.

  If the app code doesn't need to access this "temporary" Table, and the only reference to it is within this Stored Procedure, then you might consider changing it to be a real Temporary Table, which has the advantage of being cleaned up when the process completes, in which case you wouldn't need the DROP TABLE statement.

• If you will be using a permanent Table instead of a Temporary Table (in which case you need to clean it up yourself), then the DROP TABLE statement should be conditional, such that it doesn't error if the Table doesn't exist:

  IF (OBJECT_ID(N'dbo.tmpTable') IS NOT NULL)
  BEGIN
     DROP TABLE dbo.tmpTable;
  END;
  

• Rather than doing SELECT *, you should specify the full column list. Using * makes the process more likely to break when you add columns / fields to tables or subqueries (whatever aa is an alias for).