I have a large 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 an automated unique ID. Once the information is inserted into that table, I then retrieve the Unique ID that it was given, and I then used that ID as the relational field in 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.

I have been stuck on this problem for awhile, and wondering if maybe I am going about this all wrong. Is this the best way to do this or should I create a DTS Package?

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
Thanks in advanced!!