Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS : Insert/update source to destination table Expand / Collapse
Author
Message
Posted Thursday, April 23, 2009 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 23, 2009 7:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #703164
Posted Thursday, April 23, 2009 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 23, 2009 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #703194
Posted Thursday, April 23, 2009 9:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 23, 2009 5:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #703677
Posted Thursday, April 23, 2009 9:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 23, 2009 9:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #703734
Posted Friday, April 24, 2009 12:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:09 AM
Points: 205, 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);

--PS:Don't forget ';' (Semicolun) at the end.
To execute the above query you need SqlServer2008. Other wise it'll give error.
Post #703756
Posted Wednesday, April 14, 2010 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 9, 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse