A tale of two tables. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A tale of two tables.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-04 : 17:15:15

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?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-04 : 17:17:46

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57953

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-04 : 17:18:41

Use SET IDENTITY_INSERT soModsPractice ON...INSERT/SELECT...SET IDENTITY_INSERT soModsPractice OFF.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-04 : 17:22:32

Thanks again.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-04 : 17:37:00

One suggestion, instead of copying the data to a backup table them dropping the origianl, you could just us "sp_rename." That would save you a bit of IO.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-05 : 08:27:38

So, somehow I got a complete 2 complete sets of data in my somods somodspractice table, and I cannot go back.

I researched how to remove duplicate records, but every solution I found doesn't deal with a situation where your identity_column key uniqueness has been violated.

This is not a mission critical application. In fact, nobody sees it but me at the moment.

Can someone suggest how I can fix it however?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-05 : 09:17:24

Ah, I fixed it with distinct. Pulled the distinct rows to another table and copied them back.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources