February 12, 2022 at 9:33 pm
Hello,
I'm getting the below error while creating the temp table. Please advise.
Msg 8101, Level 16, State 1, Line 23
An explicit value for the identity column in table '#tmp_employees' can only be specified when a column list is used and IDENTITY_INSERT is ON.
--Actual Physical:
CREATE TABLE [dbo].[DimAccount](
[AccountKey] [int] IDENTITY(1,1) NOT NULL,
[ParentAccountKey] [int] NULL,
[AccountCodeAlternateKey] [int] NULL,
[ParentAccountCodeAlternateKey] [int] NULL,
[AccountDescription] [nvarchar](50) NULL,
[AccountType] [nvarchar](50) NULL,
[Operator] [nvarchar](50) NULL,
[CustomMembers] [nvarchar](300) NULL,
[ValueType] [nvarchar](50) NULL,
[CustomMemberOptions] [nvarchar](200) NULL,
CONSTRAINT [PK_DimAccount] PRIMARY KEY CLUSTERED
Temp table which is erroring out:
IF OBJECT_ID('tempdb..#tmp_employees') IS NOT NULL
DROP TABLE #tmp_employees
--CREATE TABLE #tmp_employees
(
[AccountKey] [int] IDENTITY(1,1) NOT NULL,
[ParentAccountKey] [int] NULL,
[AccountCodeAlternateKey] [int] NULL,
[ParentAccountCodeAlternateKey] [int] NULL,
[AccountDescription] [nvarchar](50) NULL,
[AccountType] [nvarchar](50) NULL,
[Operator] [nvarchar](50) NULL,
[CustomMembers] [nvarchar](300) NULL,
[ValueType] [nvarchar](50) NULL,
[CustomMemberOptions] [nvarchar](200) NULL
)
INSERT INTO #tmp_employees
SELECT
[AccountKey]
,[ParentAccountKey]
,[AccountCodeAlternateKey]
,[ParentAccountCodeAlternateKey]
,[AccountDescription]
,[AccountType]
,[Operator]
,[CustomMembers]
,[ValueType]
,[CustomMemberOptions]
FROM [AdventureWorksDW2016].[dbo].[DimAccount]
Thanks!
February 12, 2022 at 11:45 pm
Why are you even setting the IDENTITY property on a temporary table that you're inserting data into that already has a PK? Are you going to insert data from another table and need keys for those?
I just don't see the point of this whole exercise.
What are you really trying to accomplish? or is this just a purely theoretical exercise?
February 14, 2022 at 5:48 am
It was an error while creating the temp tbl. this is resolved after I removed the [AccountKey] from insert.
Temp tbl improved the performance in my scenario.
February 14, 2022 at 7:28 am
It was an error while creating the temp tbl. this is resolved after I removed the [AccountKey] from insert.
Temp tbl improved the performance in my scenario.
You should have removed the Identity attribute from the AccountKey of your temp table !!! ( as you may end up with different AccountKey values in your tempdb !!! )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2022 at 8:27 pm
You should have removed the Identity attribute from the AccountKey of your temp table !!! ( as you may end up with different AccountKey values in your tempdb !!! )
This is really important. If you need to retain referential integrity (ParentAccountKey joins to the correct AccountKey etc), your data will be unreliable if you assume the new identity column will match the old one. It's possible the data will insert in exactly the same order, and the source table might have no gaps in the identity values, but that's a massive risk if you were working with real data. A single deleted row from DimAccounts would cause a gap in the identity sequence and all subsequent accountkey values in the temp table would be incorrect, even if they inserted in the same order.
If you don't need the identity attribute in the new table, remove it from the table definition and include it in the insert as you did originally.
If for some reason you really do want to insert into a new table with the identity attribute, then you need to use IDENTITY_INSERT which requires a column list for the insert and select.
SET IDENTITY_INSERT #tmp_employees ON
INSERT #tmp_employees ( AccountKey, ParentAccountKey, AccountCodeAlternateKey,
AccountDescription, AccountType, Operator, CustomMembers,
ValueType, CustomMemberOptions )
SELECT AccountKey, ParentAccountKey, AccountCodeAlternateKey, AccountDescription,
AccountType, Operator, CustomMembers, ValueType, CustomMemberOptions
FROM dbo.DimAccount
SET IDENTITY_INSERT #tmp_employees OFF
February 22, 2022 at 11:04 am
This was removed by the editor as SPAM
February 24, 2022 at 5:24 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply