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 |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2006-08-04 : 17:44:27
|
| I have two tmptables that are created and linked in a stored procedure that are linked by a TMP#TMPTBL1(TMPID{PK}#TMPTBL2(TMPID{FK},SOMECOL)I need to keep the relationship and have these two tables insert their values into the permanent tables. PERMTABLE1(ID{PK-Identity Field})PERMTABLE2(ID{FK},SOMECOL)Please keep in mind that the TMPID value is reset each time I run the stored procedure, so it is not a option to use it as a referance after the values are added to the perm tables, and the SP Completes.This is to allow me to generate invoices in the temp tables, then once the generation is complete, I will add the newly created invoices to the perm tables.What is the correct approach to resolve this?THANKS! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-08-04 : 18:58:22
|
| What I mean by reset, is that each time my stored procedre is ran, the new tmp tables are created. The TMPID Is a identity column, so if there were 25 invoices my TMPID's would be (1....25). This is true for the next time I run the SP too, that is why I said that the tmpID field can only be used to referance the relationship at the time of the insert into the perm tables. For this scenerio, there is really will not be a benefit to posting the ddl. The only decent way I can see to fix my issue is to use permant "WRK" tables with a identity ID rather then the SP #TMPTBL's. This way the ID I used could be inserted directly into the perm tables, and the relationship would still be in tact.Let me know if you agree with this being the best option. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-08-04 : 21:30:50
|
| I can see the confusion, I'll try to address all issues.I will never use a natural key without also having a surrogate key. First I do like to use surrogate keys over natural just to ensure data integrity. I'll try to clearify my response. This is for a very large software application that deals with billing. The solution I posted does not have any re-identifing involved, it does however force me to create additional permanent tables, which I wanted to avoid.The methodology is to keep the live tables as isolated from the working tables as possible. By doing all the generation in tmp tables, it gives me a added layer of protecting and ensuring that all data has generated properly prior to it going into the live tables. The question I posted was if this was possible to do with #Tmp tables, and still keep the relation on importing into the live tables. Your sugestion of natural keys I am not comforitable with. A natural key no matter how great you believe you are generating it, when you look at it at the MACRO level, is rarley 100% FULL PROOF unless you use a built in function to generate it, where it is not based off row data. I agree I could make one where the likleyhood of a issue is slim to none, but I still almost always preffer a surrogate along with a natural key because I can get my 100% that way. Thanks for your help, and let me know if you would agree that adding permant wrk tables rather then using #tmp tables is the best option. This way my surrogate key would be generated in the wrk table, then inserted into the live table ensuring the relation stays in tact. |
|
|
|
|
|
|
|
|
Keeping Relationship while moving data.