Error w/ Auto_Increment for Creating new PK values

Question

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
;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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 · Answer 2

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