SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS : Insert/update source to destination table


SSIS : Insert/update source to destination table

Author
Message
chatwithkrishan
chatwithkrishan
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 292
Hi i m very new in SSIS

Table A ( Source)

Sid Mid Fname Age Address
102 2 Sam 23 34 Sector
522 4 John 23 20 Sector
423 5 David 23 22 Sector
102 1 Sam 23 34 Sector
102 2 Sam 23 54 Sector


Table B (Destination)

Sid Mid Fname Address



Table B

Sid and Mid are primary keys

I want to insert values in to “Table B” from “Table A” based on the primary keys.

If the data is not exists in “Table B” I want to insert values from “TableB”
If the data is exists in “Table B” I want to update values from “TableB”

I want to implement this by SSIS with out using Cursor. Because it reduce the performance.

Which component can i use in SSIS , and how ?

Tell me in step by step, I am very new in SSIS
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18961 Visits: 20460
If both of your tables are in SQL Server, just issue the relevant INSERT and UPDATE queries using Execute SQL tasks ...

Or avoid SSIS completely and use a standard SQL job.

If your source is not on SQL Server, I would suggest loading all of the data into a SQL Server staging table and then doing the INSERTs/UPDATEs from there.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
chatwithkrishan
chatwithkrishan
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 292
Thanks for replay,

The both table are in sql server itself. how to use Execute sql task for this.

explain me some more.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18961 Visits: 20460
You really are new!

I suggest that you search around on the Internet for something like "Creating your first SSIS package" to find the sort of step-by-step detail that you are looking for.

Once you get an idea of the development environment and how things fit together, you will probably be able to work it out for yourself: you just drag it to the Control Flow window and configure a few parameters (connection details and the SQL statement itself at a minimum, IIRC)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
chatwithkrishan
chatwithkrishan
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 292
hi,
i created Execute sql task and i retrieve value from source table.

i also tried with insert values into destination table foreach loop container
but i can t do that.


so,how can i completed this by Execute sql task
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18961 Visits: 20460
You do not need to extract data into a holding area first - just do it all in one statement, eg (untested):

INSERT into [dbname].[schema].target(Field1, Field2, ... Fieldn)
SELECT Field1, Field2, ... Fieldn from [dbname].[schema].source Source
WHERE Source.PK not in (SELECT PK from [dbname].[schema].target)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
chatwithkrishan
chatwithkrishan
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 292
hi,
thanks for ur reply.

i also tried this query as bellow.becoz i have to check 2 primary columns
[
insert into Source ( batch_id,First_Name,Last_Name,Sid,Mid)
select s.batch_id,s.First_Name,s.Last_Name,s.Street_Address1,s.DOB,s.Sex,s.Subsid,s.Mid from
Source s WHERE not exists
(select f.batch_id,f.First_Name,f.Last_Name, f.Sid,f.Mid from
destination f where f.batch_id=s.batch_id)

]

but, there is problem

1) suppose at the first time, the destination table doesn't have any data.and the source table have new record for a person and updated record for the person then, it insert all the data into destination..

so,i think, i have to insert row by row..

how can i implement this ??
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18961 Visits: 20460
No need for row by row, wash your mouth out! :-) Just use an INNER JOIN on both fields for the update (run this first).

UPDATE t
Set field1 = s.field1, field2 = s.field2
FROM SourceTable S
JOIN TargetTable T on S.Key1 = T.Key1 and S.Key2 = T.Key2



Then use a LEFT JOIN on both the fields to check for non-existent records in the target table (target PK field will be NULL) so that you know which records to INSERT.

INSERT TargetTable(Field1, Field2)
SELECT S.Field1, S.Field2
From SourceTable S
Left Join TargetTable on S.Key1 = TargetTable.Key1 and S.Key2 = TargetTable.Key2
Where TargetTable.Key1 Is Null



All this code is untested, but hopefully you get the idea.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ashokdasari
ashokdasari
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 137
Hi,
Your Requirement is fulfilled easily in SQLserver2008.
In SQLserver2008 there is a concept called Merge.with this the performane is also improoved.
Try the following query.

MERGE INTO TableB AS Target
USING TableA AS Source
ON Target.Sid =Source.Sid AND Target.Mid =Source.Mid
WHEN MATCHED THEN
UPDATE SET Target.Fname = Source.Fname ,
Target.Age = Source.Age ,
Target.Address = Source.Address
WHEN NOT MATCHED BY TARGET THEN
INSERT (Sid,Mid,Fname,Age,Address)
VALUES (Source.Sid,Source.Mid,Source.Fname,
Source.Age,Source.Address);

--PSBigGrinon't forget ';' (Semicolun) at the end.
To execute the above query you need SqlServer2008. Other wise it'll give error.
lbouaziz
lbouaziz
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 20
Hi,
a solution you might do using completely SSIS data flow component should be like that :

add a data flow task

in the data flow task add an ole db source for your table A

connect it to a lookup task

in the lookup task configure it to match your table B on your keys field

in the lookup you have three output possible :

Match , no match and error if you ve configured it.

connect the no match to an oleb destination (table b)

connect the match to an execute ole db task for your update.

that's all.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search