Need to load bulk Data with some conditions

indian143

Hi All, I have three Tables, TableA, TableB and TableC, TableA is loaded from a huge Flat file, TableB is Temp Table or Table Variable, TableC is the Table which is in Production. I want to load only those records into TableB which are not existing in TableC, I have to check all the columns of the TableC for loading into TableB, means if there are duplicate rows coming from TableA (or from Flat file which already exists in TableC) then those shouldn't be loaded into TableB, we need to check the duplication for all the Columns in every row. Any advice will be greatly helpful, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mycroft Holmes

What have you tried or what ideas do you have? One way would be to use a left outer join on all the fields and records that are null will need inserting.

Never underestimate the power of human stupidity RAH

indian143

Hi, I am planning to try this way

select * from SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] a
inner join [EOC].[FalloutSolicitationResponseErrorDetail] b
on b.CoveredID=a.[0 Covered ID]
and b.CoveredindividualsInsuranceIDorCardnumber=a.[1 Covered individuals Insurance ID/card number]
and b.UserDefinedField2=a.[2 User Defined field 2]
and b.SSNorITIN=a.[3 SSN or ITIN]
and b.FirstName=a.[4 First Name]
and b.MiddleName=a.[5 Middle Name]
and b.LastName=a.[6 Last Name]
and b.Suffix=a.[7 Suffix]
and b.DateofBirth=a.[8 Date of Birth]
and b.UserDefinedField1=a.[9 User Defined field 1]
and b.UserDefinedField3=a.[10 User Defined field 3]
and b.CoveredUserDefinedField1=a.[11 Covered User Defined field 1]
and b.CoveredUserDefinedField2=a.[12 Covered User Defined field 2]
and b.CoveredUserDefinedField3=a.[13 Covered User Defined field 3]
and b.CoveredUserDefinedField4=a.[14 Covered User Defined field 4]
and b.Comments=a.[15 Comments]
and b.SolicitationRequiredIndicator=a.[16 Solicitation Required indicator]
and b.ErrorMessage=a.[17 Error Message]

Put this query with not exists condition, but I have only rough ideas but if something good comes in I would love to use it though or while loop with Table Variable is another option, not sure if it effects the performance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

indian143

Hey buddy, I tried this and mostly +ve that it should be working, but if you find any flaws in it please point me out, related to anything in this Code.

ALTER procedure [EOC].[UpdateFalloutSolicitationResponseErrorDetail] (@FullFileName nvarchar(max)='')
AS
BEGIN
TRUNCATE TABLE Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
TRUNCATE TABLE Saw_raw.[EOC].[WorkInserted]

DECLARE @TeamId bigint =0, @MaxInstanceIdFromSaw\_2 bigint=0,  @ActivityId bigint=0
, @BatchId bigint=0, @StatusId int=0, @CreatedbyAccountId int=0, @DepartmentId int=1, @PieceCount int=1, @RecordCount int =0;

SELECT top 1 @TeamId=Id FROM SAW\_2.\[dbo\].\[Team\] WHERE TeamName='MEP - Small Group' AND DepartmentId=@DepartmentId
SELECT top 1 @ActivityId=Id FROM SAW\_2.\[dbo\].Activity WHERE ActivityName='EoC – Facets Inbound Error Report' AND TeamId=@TeamId
SELECT top 1 @BatchId=Id FROM SAW\_2.\[dbo\].Batch WHERE BatchName ='EoC Fallout Error Reports'
SELECT top 1 @CreatedbyAccountId=Id FROM SAW\_2.\[dbo\].Account WHERE LanId ='SYSTEM'
select @StatusId=Id from SAW\_2.\[dbo\].\[Status\] WHERE StatusName='New'

IF  EXISTS (
SELECT \* FROM sys.key\_constraints
WHERE type = 'PK'
AND OBJECT\_NAME(parent\_object\_id) = 'FalloutSolicitationResponseErrorDetail'
)
BEGIN	
	ALTER TABLE \[EOC\].\[FalloutSolicitationResponseErrorDetail\]
	DROP CONSTRAINT PK\_FalloutSolicitationResponseErrorDetail\_InstanceId
END
IF EXISTS (SELECT name FROM sys.indexes  
		WHERE name = N'IX\_FalloutSolicitationResponseErrorDetail\_CoveredID')
BEGIN
	DROP INDEX IX\_FalloutSolicitationResponseErrorDetail\_CoveredID ON \[EOC\].\[FalloutSolicitationResponseErrorDetail\];   
END
IF EXISTS (SELECT name FROM sys.indexes  
		WHERE name = N'IX\_FalloutSolicitationResponseErrorDetail\_CoveredindividualsInsuranceIDorCardnumber')
BEGIN
	DROP INDEX IX\_FalloutSolicitationResponseErrorDetail\_CoveredindividualsInsuranceIDorCardnumber ON \[EOC\].\[FalloutSolicitationResponseErrorDetail\];   
END
IF EXISTS (SELECT name FROM sys.indexes  
		WHERE name = N'IX\_FalloutSolicitationResponseErrorDetail\_SSNorITIN')
BEGIN
	DROP INDEX IX\_FalloutSolicitationResponseErrorDetail\_SSNorITIN ON \[EOC\].\[FalloutSolicitationResponseErrorDetail\];   
END
IF EXISTS (SELECT name FROM sys.indexes  
		WHERE name = N'IX\_FalloutSolicitationResponseErrorDetail\_LastName')
BEGIN
	DROP INDEX IX\_FalloutSolicitationResponseErrorDetail\_LastName ON \[EOC\].\[FalloutSolicitationResponseErrorDetail\];   
END
IF EXISTS (SELECT name FROM sys.indexes  
		WHERE name = N'I