SSIS : Insert/update source to destination table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS : Insert/update source to destination...

27 posts, Page 1 of 3

1 2 3 »»»

SSIS : Insert/update source to destination table

Rate Topic

Display Mode

Topic Options

Author

Message

chatwithkrishan

Posted Thursday, April 23, 2009 7:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 6:50 AM
Points: 38, 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

Post #703130

Phil Parkin

Posted Thursday, April 23, 2009 7:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 4,242, Visits: 9,492

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #703164

chatwithkrishan

Posted Thursday, April 23, 2009 7:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 6:50 AM
Points: 38, 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.

Post #703171

Phil Parkin

Posted Thursday, April 23, 2009 7:50 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 4,242, Visits: 9,492

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #703194

chatwithkrishan

Posted Thursday, April 23, 2009 9:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 6:50 AM
Points: 38, 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

Post #703365

Phil Parkin

Posted Thursday, April 23, 2009 5:21 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 4,242, Visits: 9,492

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #703677

chatwithkrishan

Posted Thursday, April 23, 2009 9:47 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 6:50 AM
Points: 38, 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 ??

Post #703727

Phil Parkin

Posted Thursday, April 23, 2009 9:58 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 4,242, Visits: 9,492

No need for row by row, wash your mouth out! Smile

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #703734

ashokdasari

Posted Friday, April 24, 2009 12:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:09 AM
Points: 190, Visits: 130

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);

--PS:Don't forget ';' (Semicolun) at the end.
To execute the above query you need SqlServer2008. Other wise it'll give error.

Post #703756

lbouaziz

Posted Wednesday, April 14, 2010 1:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 09, 2011 2:30 AM
Points: 4, 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.

Post #902913

« Prev Topic | Next Topic »

27 posts, Page 1 of 3

1 2 3 »»»

Permissions