September 2, 2011 at 11:13 am
Hi I got a scenario where
I have four tables
Table A , Table B,Table C, Table D and Table E
Table A sturcture will be like
name---ID---DateAdded
Table B
ID---CreatedDate
Table C
ID---UpdatedDate
Table D
LastUpdatedDate
Now here i got to design an SSIS package which will do inserts for new records and updates for updated records into table E
Logic is like if TableA.ID!=TableB.ID and TableA.DateAdded>=TableB.Date created
then Insert into Table E
else if TableA.id=TableC.ID and TableC.updatedDate>TableD.lastUpdatedDate
then update that ID in table E
I tried to use Lookup transformation or conditional split but wasn't successful.
Can any one suggest me the right path to think for this please.
September 2, 2011 at 11:39 am
You have 3 tables, A, B, C, D, and E? :hehe:
I'm sure that there is more to your Table Structure than you provided.
Please provide and click on the first link in my signature block. 🙂
Why do you have to do this in SSIS?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2011 at 12:20 pm
Create Table A
(
Name varchar(5) null,
ID Int null,
DateCreated Datetime null
)
insert into A
Values('Card',1,'2011-09-02 13:48:22.320')
insert into A
Values('Comm',2,'2011-09-01 12:35:12.367')
insert into A
Values('Synt',3,'2011-09-02 11:20:36.128')
insert into A
Values('Rubi',4,'2011-09-02 09:48:23.417')
Table A is the actual Table
Table B
Create Table B
(
ID int null,
LastInsert Datetime Null
)
insert into B
Values(1,'2011-09-02 13:48:22.320')
Table B Contains the values of the last insert that happened in the table A. So with the above information we have (this is not a logged operation we do it manually after we run the package)
ID--Date
1 -- '2011-09-02 13:48:22.320'
in Table B
Now we make a change in Table A for Id =2
Update A
Set Name='Royy' where id=2
and this update is done at 2011-09-02 14:01:48.150
Now we have that loged in Table C
Create table C
(
ID int null,
UpdatedDate datetime null
)
Insert into C
Values(2,'2011-09-02 14:01:48.150')
Now we have that loged into Table C as each and every update are logged into Table C automaticaly
Table D is the table which has the last Update information from Table C
Initialy we don't have anything in table D but when we couple of updates don then this Table will get filled up with the last update
(this is not a logged operation we do it manually after we run the package)
For now we have
Create table D
(
ID int null,
LastUpdatedtime datetime null
)
Insert into D
Values(2,'2011-09-02 14:01:48.150')
--- Now here is the scenario
Initially we copied the entire data From Table A to Table E(same structure Of Table A)
Now we had an insert an update in table A
Like
Insert--
insert into A
Values('Rag',5,'2011-09-02 14:09:28.567')
Update--(update done at 2011-09-02 14:11:06.503)
Update A
Set Name='Synn' where id=3
Now this update is logged into table C
Table C gets filled with
ID---Date
3---2011-09-02 14:11:06.503
Now i got to insert into table E based on the below condition
if table A.id!= Table B.id and A.dateadded>b.inserteddate
then we have to insert that record into table E
and if a.id=c.id and c.updateddate>d.lastupdated
then we have to update that records into table E
September 2, 2011 at 7:46 pm
I'm not sure where your getting your values from but basically you want to do the following.
For the INSERT Perform an INSERT followed by a JOIN of all tables and a WHERE CLAUSE.
For the UPDATE you have an UPDATE, SET and a FROM and JOIN the Tables and include a WHERE Clause.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply