September 13, 2017 at 1:35 pm
I have a table which is 175GB in size. I am working on getting only the data from August and on. INSERT INTO [Marketo].[EmailActivity_Temp]
SELECT * from [Marketo].[EmailActivity] where DateAdded > '2017-08-01'
I am getting this error
An explicit value for the identity column in table 'Marketo.EmailActivity_Temp' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Even when I turn it on, I still get the same error. Can someone help?
Temp table structure.CREATE TABLE [Marketo].[EmailActivity_Temp](
[id] [bigint] NULL,
[leadId] [bigint] NULL,
[activityTypeId] [bigint] NULL,
[primaryAttributeValueId] [bigint] NULL,
[activityDate] [datetime] NULL,
[DateAdded] [datetime] NULL,
[ProcessedInd] [char](1) NULL,
[primaryAttributeValue] [varchar](500) NULL,
[CampaignID] [bigint] NULL,
[EmailActivityKey] [bigint] IDENTITY(1,1) NOT NULL,
[NextPageToken] [varchar](100) NULL,
[LinkClicked] [varchar](2000) NULL,
[ProgramName] [varchar](500) NULL,
[Attribute] [varchar](2000) NULL,
[AssetName] [varchar](500) NULL,
PRIMARY KEY CLUSTERED
(
[EmailActivityKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
September 13, 2017 at 1:37 pm
I specified all columns plus removed EmailActivityKey column and set Identity_Insert to OFF. Let's see if it works
September 13, 2017 at 1:41 pm
Try using a column list instead of *. It's a particularly good habit to get into. You'll still need to turn IDENTITY INSERT ON for the new table, not for the old. That feature is turned on or off for a specific table. Be sure to turn it off when you're done inserting rows that way, and I just can't recall if you end up needing to reseed the table later or not. Someone else may know off the top of their head.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 13, 2017 at 1:51 pm
If this is really just a temp table why do you need the identity on the temp table, just make it a plain int with no identity and you can insert into it without anything special.
September 13, 2017 at 2:15 pm
What I am doing is a bit confusing but let me explain. I am running out of space and storage team told me to Get out and don't ask for more disk space(just kidding). We have the data we don't use so the idea was to create a temp table, insert all the data from August and on into this table, drop the old table, and rename this table. I am not sure if this is the best step. I also thought about deleting it (of course in bacthes so the log file doesn't get filled.)
September 13, 2017 at 2:40 pm
sgmunson - Wednesday, September 13, 2017 1:41 PMTry using a column list instead of *. It's a particularly good habit to get into. You'll still need to turn IDENTITY INSERT ON for the new table, not for the old. That feature is turned on or off for a specific table. Be sure to turn it off when you're done inserting rows that way, and I just can't recall if you end up needing to reseed the table later or not. Someone else may know off the top of their head.
You shouldn't need to reseed, I've used IDENTITY_INSERT when populating a new table from an existing table and the system knows to set the current value to the maximum value inserted. You could always check it afterwards to be sure using:DBCC CHECKIDENT('Marketo.EmailActivity_Temp', NORESEED)
September 14, 2017 at 1:03 pm
SET IDENTITY_INSERT [Marketo].[EmailActivity_Temp] ON;
INSERT INTO [Marketo].[EmailActivity_Temp] (
[id], [leadId], [activityTypeId], [primaryAttributeValueId], [activityDate],
[DateAdded], [ProcessedInd], [primaryAttributeValue], [CampaignID], [EmailActivityKey],
[NextPageToken], [LinkClicked], [ProgramName], [Attribute], [AssetName]
)
SELECT
[id], [leadId], [activityTypeId], [primaryAttributeValueId], [activityDate],
[DateAdded], [ProcessedInd], [primaryAttributeValue], [CampaignID], [EmailActivityKey],
[NextPageToken], [LinkClicked], [ProgramName], [Attribute], [AssetName]
from [Marketo].[EmailActivity]
where DateAdded >= '2017-08-01'
/* IF you have an index with DateAdded in it, even as just an INCLUDEd column, uncomment the next line */
/* and EmailActivityKey >= (select max(EmailActivityKey) from [Marketo].[EmailActivity] where DateAdded >= '2017-08-01') */
SET IDENTITY_INSERT [Marketo].[EmailActivity_Temp] OFF;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 26, 2017 at 10:40 am
I am pretty sure this is the Problem:
[EmailActivityKey] [bigint] IDENTITY(1,1) NOT NULL
Do you really need the IDENTITY column in the [[Marketo].[EmailActivity_Temp]/code] column? If I read your INSERT Statement properly you are trying to manually insert data into it and that could be the problem. Have you tried recreating the table and changing the datatype to a simple BIGINT column?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply