Creating a primary key field with autonumber

[andrefrancis]

Hi .....

I am creating a simple two field table from another table via the following code:

SELECT LU_CESNdx_ICD10.CES_Index AS cesdi, LU_CESNdx_ICD10.[ICD10 Code] AS icd10
FROM LU_CESNdx_ICD10;

However, I need to add an initial new primary key field called ID that is autonumbered.

Can anyone help?

..... Andre

 

[pdx_man]

CREATE TABLE dbo.YourTable (
[Your_ID] [int] IDENTITY (1, 1) NOT NULL , -- Don't name your field just ID, it is a reserved word
[cesdi] YourDataType,
[icd10] YourDataType,
CONSTRAINT [YourIndexName] PRIMARY KEY CLUSTERED
(
[Your_ID]
) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO dbo.YourTable (cesdi, icd10)
SELECT LU_CESNdx_ICD10.CES_Index,
LU_CESNdx_ICD10.[ICD10 Code]
FROM LU_CESNdx_ICD10