| Author |
Topic |
|
caid
Starting Member
10 Posts |
 Posted - 2005-02-22 : 16:12:04
|
| Excuse my ignorance, but is it possible to create a dts for replication that combines fields from more than one source table into a single destination table? eg. based on a select statement or view?e.g. In my source db, I have 3 tables:'Customers' - from which I want CustomerID and ContactName'Orders' - from which I want OrderID and OrderDate for a given CustomerID, and'OrderDetails' - from which I want ProductIDIn my destination db, I want that info to be replicated into a new table'CustomerOrders' (CustomerID, ContactName, OrderID, OrderDate, ProductID)Is it possible transform the data from the three published tables into a single table on the subscriber using a replication dts? If so, how?Regardscaid |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-22 : 16:35:51
|
| When you setup replication, the default is to use stored procedures to do the DML statements. These stored procedure can be modified to transform data. The stored procedures are named sp_MS<DMLType>_<TableName>. So if you can transform the data via T-SQL, then just modify these. I guess you could also have these stored procedures call a DTS package using DTSRun.exe via xp_cmdshell.Tara |
 |
|
|
caid
Starting Member
10 Posts |
Posted - 2005-02-22 : 17:14:41
|
| Thanks so much for your reply, Tara.So, do I have to replace the SPs for each table article? If I was replacing the INSERT for example, how do I make sure that each of the three source tables inserts the appropriate columns into the same destination table?Could you perhaps give me a very simple example of the INSERT SP contents for the Orders table I outlined above?Thanks againcaid |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-22 : 17:47:34
|
| INSERT INTO CustomerOrders(ColumnList...)SELECT c.CustomerID, c.ContactName, ...FROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDINNER JOIN OrderDetails odON o.OrderID = od.OrderIDMaybe you should consider replication the data as is to the subscriber. Then have a job that transforms the data at the subscriber side.Tara |
|
|
|
caid
Starting Member
10 Posts |
Posted - 2005-02-22 : 18:42:06
|
| well, yeah, that would be the basic query i'd run if i were looking for the join on those three tables, but I'm confused:When you're creating the publication, and you choose to publish the three tables Customers, Orders, OrderDetails etc, each of the tables has stored procs replacing the usual inserts etc. If I set each of the inserts to be an sp executing the join query as you specified above, when each of the tables is replicated, won't they each attempt to insert the same rows into the CustomerOrders table on the subscriber when the subscription is initialised?i'm starting to think maybe just replicating them over would be easiest, it just seems inefficient, because a lot of the data in the source tables are irrelevant for reporting purposes. I don't suppose you know of any reporting db whitepapers/best practice guides that I could read through?thanks for your timecaid |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-22 : 18:45:07
|
| Q1: Yes, so you would need to modify them so that this doesn't happen. I'm now thinking that using these repl stored procs isn't the way to go.Q2: I don't have any handy, but what specifically did you want to know about?Have you looked at Analysis Services? I haven't had the opportunity to use it yet, but it seems this tool would fit what you are trying to do.Tara |
|
|
|
caid
Starting Member
10 Posts |
Posted - 2005-02-22 : 19:05:37
|
| hiyai have minimal experience with sql server, i've just been assigned the task of reporting by my boss, and while my first thought was "sweet, i'll just replicate over what i need to where i need it" that seems to be more complicated than i anticipated. so i'm wondering what best practice is when setting up a reporting db. does one normally dump the source tables over as is, and mangle them on the subscriber side, or do some form of transformation or filtering on the publisher side, or what?caid |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-22 : 19:08:18
|
| Well it would depend on your requirements. Our reporting database has the same structure as the source database, so we don't have any problems with replication. Well we've got extra columns in the reporting database, but it just required a slight modification to the repl stored procedures. From what I can tell about your requirements, Analysis Services is the way to go. But be warned though that it doesn't appear many people use this product as when people ask questions here about it, the questions are rarely answered.Tara |
|
|
|
caid
Starting Member
10 Posts |
Posted - 2005-02-22 : 19:20:14
|
| Thanks so much for your help.Much appreciated :o)caid |
|
|
|
|
replication dts