Insert items from one to table to multiple smaller tables

Dev Shed Forums Sponsor:

November 15th, 2004, 12:51 PM

jmlsgateway

Contributing User

Join Date: Mar 2004

Location: OKC

Posts: 342

Time spent in forums: 2 Days 20 h 48 m 28 sec
Reputation Power: 5

Insert items from one to table to multiple smaller tables

I have a table that I filled with data imported from another database.

What I need to do is now take this huge table and break apart the information and put it into 5 smaller tables.

So I have a huge insert statement.

I have one main table called Property with two keys. One key is a "Prop_ID" and the other is "owner" where Prop_Id is a automated unique ID. Once the information is inserted into that table, I then get the Unique ID that it was given, and I then used that ID to insert into the other tables.

The problem I am encountering is I keep getting the following error

Violation of PRIMARY KEY constraint 'PK_Prop_Res_Detail'. Cannot insert duplicate key in object 'Prop_Res_Detail'.
The statement has been terminated.

I have an idea what might be going wrong, but I am not sure. What I want to happen is that I want the query to look at the first row of the huge table and then do all 4 of the inserts, and then go to the next row. But I think it is trying to all the inserts into the property table, and then go on to the Prop_Res_Detail table and that is why I am getting that error.

Any help is greatly appreicated.

here is the code..

Code:

CREATE PROCEDURE [dbo].[Insert_Properties] 

AS

DECLARE @Prop_ID Int

SET NOCOUNT ON

INSERT INTO Property(	Acres,
			Assoc_Phone,
			Assoc_Cell,
			AppraisalForm,
			Area,
			Assess_Account,
			AttachDetach,
			Block,
			City,
			County,
			Directions,
			DOM,
			ER_EA,
			FloodZone,
			Import_From,
			Import_ID,
			Insert_Date,
			LandSQFT,
			LandSQFTDim,
			LegalRemarks,
			ListAppraiser_ID,
			ListAssoc_ID,
			ListBroker_ID,
			ListDate,
			Listing_Office_Remarks,
			ListPrice,
			Lot,
			Map,
			Num_Images,
			Office_Phone,
			Original_ListPrice,
			Owner,
			Pending_Date,
			PhotoName,
			PropSubType,
			Prop_Type,
			Quad,
			Remarks,
			State,
			Status,
			StreetDir,
			StreetNum,
			StreetName,
			Township,
			UnitNumber,
			ZipCode)

SELECT CONVERT(FLOAT(8), Acres),
	 CONVERT(Varchar(25), Assoc_Phone),
	 CONVERT(Varchar(25),Assoc_Cell),
	 CONVERT(Varchar(50), AppraisalForm),
	 CONVERT(Varchar(10), Area),
	 CONVERT(Varchar(50), Assess_Account),
	 CONVERT(Varchar(20), AttachDetach),
	 CONVERT(Varchar(20), Block),
	 CONVERT(Varchar(40), City),
	 CONVERT(Varchar(50), County),
	 CONVERT(Varchar(1000), Directions),
	 CONVERT(int, DOM),
	 CONVERT(Varchar(10), ER_EA),
	 CONVERT(Varchar(50), FloodZone),
	 CONVERT(Varchar(20), Import_From),
	 CONVERT(Varchar(20), Import_ID),
	 CONVERT(datetime, Insert_Date, 101),
	 CONVERT(Varchar(20), LandSQFT),
	 CONVERT(Varchar(50), LandSQFTDim),
	 CONVERT(Varchar(2000), LegalRemarks),
	 CONVERT(Varchar(50), ListAppraiser_ID),
	 CONVERT(Varchar(50), ListAssoc_ID),
	 CONVERT(Varchar(50), ListBroker_ID),
	 CONVERT(varchar(11), ListDate),
	 CONVERT(Varchar(1000), Listing_Office_Remarks),
	 CONVERT(Varchar(10), ListPrice),
	 CONVERT(Varchar(20), Lot),
	 CONVERT(Varchar(10), Map),
	 CONVERT(Varchar(10), Num_Images),
	 CONVERT(Varchar(25), Office_Phone),
	 CONVERT(Varchar(10), Original_ListPrice),
	 CONVERT(Varchar(50), Owner),
	 CONVERT(datetime, Pending_Date, 101),
	 CONVERT(Varchar(50), PhotoName),
	 CONVERT(Varchar(25), PropSubType),
	 CONVERT(Varchar(20), Prop_Type),
	 CONVERT(Varchar(10), Quad),
	 CONVERT(Varchar(1000), Remarks),
	 CONVERT(Varchar(25), State),
	 CONVERT(Varchar(10), Status),
	 CONVERT(Varchar(4), StreetDir),
	 CONVERT(Varchar(15), StreetNum),
	 CONVERT(Varchar(50), StreetName),
	 CONVERT(Varchar(20), Township),
	 CONVERT(Varchar(6), UnitNumber),
	 CONVERT(Varchar(20), ZipCode )

FROM Imported_Closed_Property_From_MLS


SET @Prop_ID = @@Identity

/*Property Res Table */
INSERT INTO Prop_Res_Detail(	Prop_ID,
				Addition,
				Appliances,
				Basement_Area, 
				BasementDesc,
				Builder,
				Construction,
				Cool,
				Dining,
				District_School,
				Energy,
				Exterior_Features,
				Fence,
				Floors,
				Foundation,
				FP,
				FP_Type,
				Garage_Attach_Detach,
				Garage_Cap,
				Handicap,
				Heat,
				HOA,
				HOA_Fee,
				HOA_Inc,
				HOA_Period,
				Inlaw_Plan,
				Interior_Features,
				Livestock,
				Lot_Desc,
				Mechanical,
				NumLivingArea,
				Num_Baths,
				Num_Beds,
				Num_Levels,
				Other_Info,
				OvenDesc,
				Owner,
				Parking,
				Patio,
				Patio_Dim,
				Perc_Basement_Com,
				Pool,
				Pool_Type,
				Prop_Faces,
				Range,
				RangeDesc,
				Remodeled,
				Rental,
				RentalAmount,
				Roof_Type,
				Roof_Year,
				RoomOther,
				Sect,
				SQFT,
				SQFTSource,
				Style,
				Tax_Amount,
				Tot_Rooms,
				UtilityAvailable,
				WindowType,
				Year_Built)

SELECT @Prop_ID,
	 CONVERT(Varchar(50), Addition),
	 CONVERT(Varchar(100), Appliances),
	 CONVERT(Varchar(25), Basement_Area),
	 CONVERT(Varchar(100), BasementDesc),
	 CONVERT(Varchar(50), Builder),
	 CONVERT(Varchar(50), Construction),
	 CONVERT(Varchar(20), Cool),
	 CONVERT(Varchar(10), Dining),
	 CONVERT(Varchar(60), District_School),
	 CONVERT(Varchar(100), Energy),
	 CONVERT(Varchar(100), Exterior_Features),
	 CONVERT(Varchar(40), Fence),
	 CONVERT(Varchar(100), Floors),
	 CONVERT(Varchar(40), Foundation),
	 CONVERT(Varchar(50), FP),
	 CONVERT(Varchar(40), FP_Type),
	 CONVERT(Varchar(50), Garage_Attach_Detach),
	 CONVERT(Varchar(25), Garage_Cap),
	 CONVERT(Varchar(20), Handicap),
	 CONVERT(Varchar(20), Heat),
	 CONVERT(Varchar(40), HOA),
	 CONVERT(Varchar(30), HOA_Fee),
	 CONVERT(Varchar(100), HOA_Inc),
	 CONVERT(Varchar(20), HOA_Period),
	 CONVERT(Varchar(20), Inlaw_Plan),
	 CONVERT(Varchar(100), Interior_Features),
	 CONVERT(Varchar(40), Livestock),
	 CONVERT(Varchar(400), Lot_Desc),
	 CONVERT(Varchar(100), Mechanical),
	 CONVERT(Varchar(10), NumLivingArea),
	 CONVERT(Varchar(5), Num_Baths),
	 CONVERT(Varchar(5), Num_Beds),
	 CONVERT(Varchar(30), Num_Levels),
	 CONVERT(Varchar(100), Other_Info),
	 CONVERT(Varchar(100), OvenDesc),
	 CONVERT(Varchar(50), Owner),
	 CONVERT(Varchar(100), Parking),
	 CONVERT(Varchar(25), Patio),
	 CONVERT(Varchar(50), Patio_Dim),
	 CONVERT(Varchar(25), Perc_Basement_Com),
	 CONVERT(Varchar(20), Pool),
	 CONVERT(Varchar(20), Pool_Type),
	 CONVERT(Varchar(40), Prop_Faces),
	 CONVERT(Varchar(20), Range),
	 CONVERT(Varchar(100), RangeDesc),
	 CONVERT(Varchar(50), Remodeled),
	 CONVERT(Varchar(10), Rental),
	 CONVERT(Varchar(10), RentalAmount),
	 CONVERT(Varchar(20), Roof_Type),
	 CONVERT(Varchar(5), Roof_year),
	 CONVERT(Varchar(100), RoomOther),
	 CONVERT(Varchar(10), Sect),
	 CONVERT(Varchar(10), SQFT),
	 CONVERT(Varchar(50), SQFTSource),
	 CONVERT(Varchar(100), Style),
	 CONVERT(Varchar(10), Tax_Amount),
	 CONVERT(Varchar(5), Tot_Rooms),
	 CONVERT(Varchar(100), UtilityAvailable), 
	 CONVERT(Varchar(50), WindowType),
	 CONVERT(Varchar(5), Year_Built)
FROM Imported_Closed_Property_From_MLS

/*Sold Info Table */
INSERT INTO Sold_Info(Prop_ID,
			Buy_Pts,
			Closed_Date,
			Closed_Price,
			Closed_Price_SQFT,
			COOP_Sales,
			Days_On_Market,
			InterestRate,
			Lender,
			LoanAmount,
			LoanTerms,
			Loan_Years,
			Origination_Fee,
			Owner,
			SellerConcessions,
			LoanType,
			Sold_Remarks)

SELECT @Prop_ID,
	 CONVERT(Varchar(10), Buy_Pts),
	 CONVERT(datetime, Closed_Date, 101),
	 CONVERT(Varchar(10), Closed_Price),
	 CONVERT(Varchar(50), Closed_Price_SQFT),
	 CONVERT(Varchar(50), COOP_Sales),
	 CONVERT(Varchar(5), DOM),
	 CONVERT(Varchar(10), InterestRate),
	 CONVERT(Varchar(50), Lender),
	 CONVERT(Varchar(10), LoanAmount),
	 CONVERT(Varchar(50), LoanTerms),
	 CONVERT(Varchar(10), Loan_Years),
	 CONVERT(Varchar(10), Origination_Fee),
	 CONVERT(Varchar(50), Owner),
	 CONVERT(Varchar(100), SellerConcessions),
	 CONVERT(Varchar(25), LoanType),
	 CONVERT(Varchar(1000), Sold_Remarks)
FROM Imported_Closed_Property_From_MLS

/*Remarks Table */
INSERT INTO Remarks(Prop_ID,
				App_Date,
				App_Remark,
				Contract_Date,
				Inspection_Type,
				Owner,
				PendingSalesPrice,
				PendingSaleComments)

SELECT  @Prop_ID,
	   CONVERT(datetime, App_Date, 101),
	   CONVERT(Varchar(1000), App_Remark),
	   CONVERT(datetime, Contract_Date, 101),
	   CONVERT(Varchar(50), Inspection_Type),
	   CONVERT(Varchar(50), Owner),
	   CONVERT(Varchar(10), PendingSalesPrice),
	   CONVERT(Varchar(1000), PendingSaleComments)
FROM Imported_Closed_Property_From_MLS

GO

November 16th, 2004, 06:34 PM

DPAC750

Contributing User

Join Date: Nov 2004

Posts: 31

Time spent in forums: 5 h 55 m 16 sec
Reputation Power: 4

You have 2 Options,
1) Turn off all of the Primary Key Constraints, insert, turn on all key constraints
2) Use cursors

The issue with doing #1 is if you are missing anything, it could mess things up and make turning on the key constraints a pain.

This is a great resource on how to use a cursor:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp

Basically you will need to insert 1 row into all of your small tables upon every loop of the main cursor. If this still has issues(because if it is your Primary Key, then it means you have duplicates of some sort, not a Foreign Key issue which is what you are describing) Then it will error out or stop at the item that has caused the issue. This will easily allow you to figure out what is causing the issue since you will know the exact row that caused it. However, if you do error out in the middle it WILL NOT roll back so all of the items that have been inserted already will still be in there.

Good Luck

November 18th, 2004, 08:05 AM

jmlsgateway

Contributing User

Join Date: Mar 2004

Location: OKC

Posts: 342

Time spent in forums: 2 Days 20 h 48 m 28 sec
Reputation Power: 5

Thanks for the response!

I didn't know anything about a cursor, so I looked that up.

I did something like the following

Code:

DECLARE @Import_ID  int

DECLARE cRow CURSOR FOR
SELECT Import_ID
FROM Imported_Closed_Property_From_MLS

OPEN cRow

FETCH NEXT FROM cRow
INTO @Import_ID

WHILE @@FETCH_STATUS = 0
BEGIN

	EXEC Insert_Properties_Into_AIE @Import_ID
	FETCH NEXT FROM cRow
	INTO @Import_ID
END

CLOSE cROW
DEALLOCATE cROW

Again thanks for the help!!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: