CODEWITHSUNDEEP
sqlsql-server-2008ssis
Sravanthi
Sravanthi

Reputation: 45

Moving data in one table to another table Based on condition

I need help in figuring out the solution for my problem.

1. I have a 4 tables 

a. CLientdetails
b. ClientAccountDetails
c. ClientExistingData
d. ClientNotExistindata.

Clinet details contains data related to the name of the client, startingdate, endingDate

ClientAccountDetails contains data related to specific client(ie multiple accounts asscoiated with his name, status of the account, money)

using SSIS package i import data from Excel sheet(data related to excel sheet is of ClientAccountDetails) into a different table called IMPORTClientDetails.

when all the data gets imported to the table using ssis package. i would write some query to move that into the ClientAccountDetails table based on the Name of the Client.

2. My question is

If the Client name from IMPORTClientDetails doesnot exist in the ClientDetails table i want the data to be moved to ClientNotExistindata

if the Client name from IMPORTClientDetails exist then i want to move that into ClientAccountDetails

Upvotes: 0

Views: 2786

Answers (2)

grapefruitmoon
grapefruitmoon

Reputation: 3008

In SSIS you can use the Lookup Task to check if the imported ClientName rows from IMPORTClientDetails exist in ClientDetails - if there is no match, they can be sent to ClientNotExistindata. The matching rows can then be sent to ClientAccountDetails.

Upvotes: 1

John
John

Reputation: 5834

This can be done with two simple INSERT/SELECT statements.

INSERT INTO ClientAccountDetails(COLUMN1,COLUMN2, ETC)
SELECT COLUMN3,COLUMN4, ETC
FROM IMPORTClientDetails ICD
WHERE ICD.Client IN (SELECT Client FROM ClientDetails);

INSERT INTO ClientNotExistindata(COLUMN1,COLUMN2, ETC)
SELECT COLUMN3,COLUMN4, ETC
FROM IMPORTClientDetails ICD
WHERE ICD.Client NOT IN (SELECT Client FROM ClientDetails);

Upvotes: 1

Related Questions