April 8, 2010 at 4:16 am
Hi Please help
I know this is very basic but I'm getting errors and I need help.
Snapshorts of the production database are created every month so what I need to do is create a single table "Area" with all Area tables form the snapshots.
the original table would have :
--Table name = Areas
col1 (pk, int, not null)
col2 (varchar(300),null)
col3 (varchar(300),null)
col4 (int,null)
On a seperate database I'm trying to create a historical table with the month as period. so it should something like:
--Table name = Areas
col1 (pk, int, not null)
col2 (varchar(300),null)
col3 (varchar(300),null)
col4 (int,null)
col5 (Period(18,0),null)
from every months snapshort I would then append to the table I created.
So I have created the table with the period, my only problem is that when I try to add more data/periods I get an error:
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Clusters' when IDENTITY_INSERT is set to OFF.
the query I tried is:
Insert into Areas
Select *
From snapshotdb.dbo.Areas
Please help
April 8, 2010 at 4:50 am
This is clearly visible that any column 'Clusters' is identity column
and you are trying to pass the value for that
so you just select column names instead of *
and dont take identity column in select list.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 8, 2010 at 5:08 am
I used table areas just as an example the actual table is Clusters and the structure of the table if as follows:
CREATE TABLE [dbo].[Clusters](
[ClusterID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Cluster] [varchar](300) NULL,
[DateCreated] [datetime] NULL,
[disabled] [bit] NULL,
[LockPos] [bit] NULL,
[International] [int] NULL,
[DDI] [bit] NULL,
[ClusterCode] [varchar](20) NULL,
[Period] [numeric](18, 0) NULL,
CONSTRAINT [PK_Clusters] PRIMARY KEY CLUSTERED
(
[ClusterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
I have tried selecting columns instead of using select * but 'm still getting an error
April 8, 2010 at 5:36 am
Trybbe (4/8/2010)
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Clusters' when IDENTITY_INSERT is set to OFF.
the query I tried is:
Insert into Areas
Select *
From snapshotdb.dbo.Areas
Please help
Don't you think these two statements are confusing? Please provide the complete table structure of both the tables, insert script that you are using and some sample data if possible and make sure it is not contradictory.
Help us to help you..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply