April 15, 2010 at 12:18 pm
Hi,
I have the following scenrio:
Multiple SQLExpress 2005 Databases at different Offices
One SQL 2005 (Full Version) at Head office.
I want to be able to take data from one table in all the SQLExpress servers and import into one table in the Head office server.
It seems I can do this with the copy column transformation but it has a couple of problems.
1. It duplicated data if it already exists
2. I need to add another column in the destination with the source name as the value.
What is the best way of going about this
Appreciate your time
Jason
April 15, 2010 at 12:49 pm
We need a bit more info ...
What exactly do you mean by import? Do you have matching PKs? What happens if a match is found - update or ignore? Should records ever be deleted?
Presumably, if no match is found, you just want to INSERT the new record?
Is the table structure the same between host and Express dbs? If so, why are you using Copy Column?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 15, 2010 at 1:36 pm
Hi Phil,
All the tables/Columns are the same in the remote sites (SQLEXpress servers). I am creating a table in the central SQL server the same.
All records contain a unique ID. I don't want to delete any records but update overnight the central table to contain all records in the remote sites so I can report on them in one place.
I am only looking at copy column because it looked like an obvious choice. As you can tell I am a newbie re this and open to any ideas
Thanks again
Jason
April 15, 2010 at 3:27 pm
OK Rookie, I'll go easy on you 🙂
If a matching record is found (based on ID), do you just want to ignore it? Or perform some sort of update?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 15, 2010 at 10:56 pm
Thanks 🙂
If a record is found I want to ignore. Records wont change once entered.
Cheers
April 16, 2010 at 12:18 am
zuma01 (4/15/2010)
If a record is found I want to ignore. Records wont change once entered.Cheers
I think you can just use an OLE DB Source to read the data. Do this with an SQL statement and immediately join it with your destination table on the business key (or PK, whatever suits your solution). Make sure you put the with(nolock) option, to avoid deadlocks. From the destination table, take the surrogate key (or PK).
After the source, place a conditional split. If the SK of the destination table is NULL, it means that the row is an insert. If a SK was found, then the row is an update. In your case, you need to keep only the inserts.
Finally, place an OLE DB Destination with fastload.
If you have questions regarding this set-up, just let me know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 16, 2010 at 1:55 am
da-zero (4/16/2010)
zuma01 (4/15/2010)
If a record is found I want to ignore. Records wont change once entered.Cheers
I think you can just use an OLE DB Source to read the data. Do this with an SQL statement and immediately join it with your destination table on the business key (or PK, whatever suits your solution). Make sure you put the with(nolock) option, to avoid deadlocks. From the destination table, take the surrogate key (or PK).
After the source, place a conditional split. If the SK of the destination table is NULL, it means that the row is an insert. If a SK was found, then the row is an update. In your case, you need to keep only the inserts.
Finally, place an OLE DB Destination with fastload.
If you have questions regarding this set-up, just let me know.
You have made this more difficult than necessary, I think.
If you proceed as suggested, but instead using a LEFT JOIN and selecting source data where target PK is null, the rows can just be inserted in the knowledge that they do not exist in the target.
eg
select s.pk, s.f1, s.f2
from source s
left join target t on s.pk = t.pk
where t.pk is null
no need for the conditional split.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 16, 2010 at 2:22 am
Ha, of course you're right.
I divised my suggestion from the template that I always use. This template has a conditional split to sort out the insert and the updates. I just deleted what I didn't need, e.g. the updates, but I didn't think to simplify it further.
I can be lazy sometimes 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 16, 2010 at 2:28 am
da-zero (4/16/2010)
Ha, of course you're right.I divised my suggestion from the template that I always use. This template has a conditional split to sort out the insert and the updates. I just deleted what I didn't need, e.g. the updates, but I didn't think to simplify it further.
I can be lazy sometimes 🙂
You have templates? Wow, that's not lazy, it's organised. I make up all my cr@p every time 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 16, 2010 at 7:50 am
Thanks guys, that gives me something to go at
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply