SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 A tale of two tables.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 09/04/2008 :  17:15:15  Show Profile  Reply with Quote
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

Australia
260 Posts

Posted - 09/04/2008 :  17:17:46  Show Profile  Reply with Quote
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57953
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/04/2008 :  17:18:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 09/04/2008 :  17:22:32  Show Profile  Reply with Quote
Thanks again.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/04/2008 :  17:37:00  Show Profile  Reply with Quote
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.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 09/05/2008 :  08:27:38  Show Profile  Reply with Quote
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?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 09/05/2008 :  09:17:24  Show Profile  Reply with Quote
Ah, I fixed it with distinct. Pulled the distinct rows to another table and copied them back.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000