SQL Server – Create INTO Table with Primary Key

auto-incrementidentitysql serverstored-procedures

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

Best Answer

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 (...)
Related Question