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.
| Author |
Topic |
|
durban
7 Posts |
 Posted - 2006-04-07 : 11:07:37
|
| We have two staging tables (Parent and Child) that contain both insert and update information. The Childs parent my be in either the Production or the Staging table.I need to display a DISTINCT organization name and record number from the combination of the records in Parent and the Child (just one entry per organization). If there is more than one Child, or more than one parent update, just display the parent information once. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-07 : 11:59:14
|
| Have you had a go at it?If your problem is that they are in two tables then you can just union the tables - maybe with an outer join to get distinct entries.Otherwise you will need to give a bit moore info about the structure and the result you want.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
durban
7 Posts |
Posted - 2006-04-07 : 16:35:49
|
| The staging tables are used to review information entered by the users before posting to the production tables. There are two staging tables – Master and Child. The Master and the Child tables can have both New records or Updated records. To enter a Child record you need to have a Master, either in the Staging or Prod tables. So I have a mixture of records in the Staging tables. I need to build a list which has an organ name, record number from either the Prod Master (Update in Stage Master) or Stage Master (Insert)There can only be one entry in the list for each different organization whether it be in the Master (existing) or in the Stage (new).The organization can be new or existing, there can be updates/inserts to the Prod master from the Stage master and updates/inserts to the Prod child form the stage child.The problem is who do I look at first (I assume the Stage Master) and how do I join the Prod Master, Stage Master and Stage Child together to produce a single reference to a Master (whether it be Prod or Stage).Hope this helps explain my situation…..it is pretty confusing to my…….Thanks dan |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-07 : 17:07:33
|
quote:
Originally posted by durban
To enter a Child record you need to have a Master, either in the Staging or Prod tables.
Well, that's a design problem. Staging tables are for staging data, and now you are trying to use them as part of your relationional integrity. Bad karma, dude. Why are you refencing staging data that hasn't been processed yet? |
|
|
|
durban
7 Posts |
Posted - 2006-04-10 : 12:31:09
|
| I am trying to present the information in the staging tables to the owner, so that they can review and okay the information for posting to the production tables. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-10 : 12:34:42
|
| I'd still avoid referential integrity in staging tables. My philosophy is that staging tables should accept just about ANY data they are given. No constraints. Allow nulls. Whatever. Then you run a stored to verify the data and move it to production. Any data that fails verification is flagged for review. You can easily write logic into a stored proc that will check whether a corresponding value exists in either a production table or another staging table. |
|
|
|
|
|
|
|
|
Constructing a Select Statement