Error w/ Auto_Increment for Creating new PK values

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  August 9, 2017 at 10:06 am

  #100096

  Hello, Below is my code to create a table, and then insert from one table data from another, however the source table has one column I need, and the new dim targt table needs an auto_increment key to be populated in, and I have it close I think. The error I'm getting is: Invalid column name 'KEYS', and I can;t seem to get past this, can someone please advise. Thank you

  --drop table targetdimtable;
  create table targetdimtble
  (
  [KEYS] int Primary key
  , clutr_nm varchar(35) null
  )
  on [primary]
  ;
  insert into targetdimtable ([KEYS], clutr_nm)
  select
  isnull(( select max(isnull([KEYS],0)) from targetdimtable ),0) + ( ROW_NUMBER() over (order by [KEYS]) )
  , clutr_nm
  from FactSource
  ;

• Lowell

  SSC Guru

  Points: 323524

  More actions

  August 9, 2017 at 10:13 am

  #1954665

  in SQL, you don't reference the table to generate a new column value, there is an identity property that does it for you instead.
  your structure and command should look like this, and you never reference the [keys] column unless you are doing a select

  IF OBJECT_ID('[dbo].[targetdimtble]') IS NOT NULL
DROP TABLE [dbo].[targetdimtble]
GO
CREATE TABLE [dbo].[targetdimtble] (
[KEYS]  INT     IDENTITY(1,1)    NOT NULL,
[clutr_nm] VARCHAR(35)           NULL,
CONSTRAINT [PK__targetdi__6AF90CF76FD2CCBD] PRIMARY KEY CLUSTERED  ([KEYS] asc) )

  INSERT INTO [targetdimtable]
   ( [clutr_nm] )
   SELECT [clutr_nm]
   FROM  [FactSource];

  SELECT [KEYS],[clutr_nm] FROM [targetdimtable]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• quinn.jay

  SSCertifiable

  Points: 5505

  More actions

  August 9, 2017 at 3:10 pm

  #1954711

  Lowell - Wednesday, August 9, 2017 10:13 AM

  in SQL, you don't reference the table to generate a new column value, there is an identity property that does it for you instead.
  your structure and command should look like this, and you never reference the [keys] column unless you are doing a select

  IF OBJECT_ID('[dbo].[targetdimtble]') IS NOT NULL
DROP TABLE [dbo].[targetdimtble]
GO
CREATE TABLE [dbo].[targetdimtble] (
[KEYS]  INT     IDENTITY(1,1)    NOT NULL,
[clutr_nm] VARCHAR(35)           NULL,
CONSTRAINT [PK__targetdi__6AF90CF76FD2CCBD] PRIMARY KEY CLUSTERED  ([KEYS] asc) )

  INSERT INTO [targetdimtable]
   ( [clutr_nm] )
   SELECT [clutr_nm]
   FROM  [FactSource];

  SELECT [KEYS],[clutr_nm] FROM [targetdimtable]

  Thank you, I was able to use IDENTITY to get what I needed