December 27, 2016 at 8:10 am
Hello,
I have two tables
-- Table A in Server A of Database A
-- Table B in Server B of Database B
My Source is Table A and Destination is Table B
I need to Compare Table A and Table B and insert into Table B with differences found in Table A
I'm using Lookup transformation in SSIS and sending the unmatched rows to Table B
There are matched rows too. but I just want to send unmatched rows only... since matched rows are in millions
December 27, 2016 at 9:02 am
wweraw25 (12/27/2016)
Hello,I have two tables
-- Table A in Server A of Database A
-- Table B in Server B of Database B
My Source is Table A and Destination is Table B
I need to Compare Table A and Table B and insert into Table B with differences found in Table A
I'm using Lookup transformation in SSIS and sending the unmatched rows to Table B
There are matched rows too. but I just want to send unmatched rows only... since matched rows are in millions
What are you matching on? Is it an incrementing key?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 27, 2016 at 9:03 am
Quick question, are the rows identical or are you comparing individual columns/column combinations?
😎
December 27, 2016 at 9:22 am
I'm comparing on combination of keys.
I know lookup is not a good option for millions of rows.
Is there any other way to grab the difference and insert into table B.
December 27, 2016 at 9:52 am
wweraw25 (12/27/2016)
I'm comparing on combination of keys.I know lookup is not a good option for millions of rows.
Is there any other way to grab the difference and insert into table B.
It depends on your data. If there's no data pattern that can be used for selection of the missing rows (date created > x, col1 > y etc.), then the full comparison has to be done somewhere.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 27, 2016 at 11:01 am
Yes you are right. I have a two date fields to compare.
But whats the thing I should do in SSIS to achieve this since it is across different servers.
December 27, 2016 at 12:36 pm
wweraw25 (12/27/2016)
Yes you are right. I have a two date fields to compare.But whats the thing I should do in SSIS to achieve this since it is across different servers.
1) Create a datetime variable, MaxCreatedAt, in your package.
2) Create an ExecuteSQL task, to do the following:
Select MaxCreatedAt = Max(CreatedAt) from targettable
Assign the result of the query to the variable you created in (1)
3) Create a stored proc in your source database which takes a datetime argument (@MaxCreatedAt) and returns all the columns you are interested in,
WHERE date >= @MaxCreatedAt
4) Modify your data flow source to execute the above proc, passing the variable you just created above. You might have to use an Expression to make this work (I don't have SSDT open right now, so cannot verify).
5) You'll still need to check that you're not loading anything twice (this could happen if the datetimes are exactly equal), but the amount of data you select should be much reduced. One way of handling this is to push everything into an empty staging table and then do an
INSERT target (Col1, Col2)
SELECT Col1, Col2
from StagingTable
Where not exists (select 1 where target.MatchCols= StagingTable.MatchCols)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply