Update a table with a SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update a table with a SSIS

Author

Topic

h2sut
Starting Member

40 Posts

Posted - 2008-10-06 : 15:16:04

I have two table. Table A and Table B. Is there a way when i run my SSIS package to update Table b wiht the information from A. Table A has 60 users and table B has 100. So i would update table B with the new infromation from A.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 15:23:22

please provide more information.
tablestruktur.
columns to match (join) records in table a / table b.
columns to update.

Then, when you have a sql-statement, you can take it to your package.

Greetings
webfred

Planning replaces chance by mistake

h2sut
Starting Member

40 Posts

Posted - 2008-10-06 : 16:27:18

Okay Table A has

date ,userid, Fname
1 2 John
1 3 Frank

Okay Table B has

date, userid,Fname
2 2 John
3 3 Frank

SO my final resuts would be from A after it updates the file

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 16:40:24

-- create temp tables for test
create table #table_a(
date int,
userid int,
Fname varchar(30))

create table #table_b(
date int,
userid int,
Fname varchar(30))

-- insert test-data
insert #table_a
select 1,2,'John' union
select 1,3,'Frank'

insert #table_b
select 2,2,'John' union
select 3,3,'Frank'

-- show test-data before update
select * from #table_a
select * from #table_b

-- update
update b
set date = a.date
from #table_b as b
join #table_a as a on (a.userid = b.userid)

-- show test-data after update
select * from #table_a
select * from #table_b

-- cleaning
drop table #table_a
drop table #table_b

Planning replaces chance by mistake

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-06 : 16:45:02

Put this in Execute SQL task and run it through SSIS

UPDATE b
SET b.date=t.date,........
FROM table1 t
INNER JOIN table2 b
ON b.date=t.date
AND b.userid=t.userid
and b.Fname= t.Fname

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 16:49:18

Hi sodeep,
first i want to know, whats wrong with my solution and second:
ON b.date = t.date will not work...

webfred

Planning replaces chance by mistake

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-07 : 12:12:18

FYI, You can also do this through the Slowly Chaning Dimension control or the Lookup control.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources