I have the following two tables. Table 1 contains data:
CREATE TABLE [somodsbackup] (
[fenumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fsono] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[frelease] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fDateChanged] [datetime] NOT NULL ,
[forderqtyOLD] [numeric](15, 5) NULL ,
[forderqtyNEW] [numeric](15, 5) NULL ,
[funetpriceOLD] [numeric](17, 5) NULL ,
[funetpriceNEW] [numeric](17, 5) NULL ,
[PriceMod] [numeric](17, 5) NOT NULL ,
[ModType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FKSorelsID] [int] NOT NULL ,
[identity_column] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
Table 2 is the one I intend to replace Table 1 with. I've highlighted the difference:
CREATE TABLE [soMods] (
[fenumber] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fsono] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[frelease] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fDateChanged] [datetime] NOT NULL ,
[fstatusOLD] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fstatusNEW] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[forderqtyOLD] [numeric](15, 5) NULL ,
[forderqtyNEW] [numeric](15, 5) NULL ,
[funetpriceOLD] [numeric](17, 5) NULL ,
[funetpriceNEW] [numeric](17, 5) NULL ,
[PriceMod] [numeric](17, 5) NOT NULL ,
[ModType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FKSorelsID] [int] NOT NULL ,
[identity_column] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
Tomorrow morning, I am going to drop the original table soMods after making a copy into table 1.
I have a limited window of time to do these things while people are out of the system. When I attempted to do this on my test server I got the following error:
Insert into soModsPractice(
fenumber ,
fsono ,
frelease,
fDateChanged ,
fstatusOLD ,
fstatusNEW ,
forderqtyOLD ,
forderqtyNEW ,
funetpriceOLD ,
funetpriceNEW ,
PriceMod ,
ModType ,
FKSorelsID ,
identity_column)
select fenumber ,
fsono ,
frelease,
fDateChanged ,
'PRE' ,
'PRE' ,
forderqtyOLD ,
forderqtyNEW ,
funetpriceOLD ,
funetpriceNEW ,
PriceMod ,
ModType ,
FKSorelsID ,
identity_column
from SoMods
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'soModsPractice' when IDENTITY_INSERT is set to OFF.
Now, I don't normally want to allow Identity_colum values to be entered manually except this one time.
What do I do?
A tale of two tables.
Posted - 09/04/2008 : 17:15:15
