August 9, 2017 at 10:06 am
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
;
August 9, 2017 at 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]
Lowell
August 9, 2017 at 3:10 pm
Lowell - Wednesday, August 9, 2017 10:13 AMin 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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy