January 4, 2012 at 7:57 am
I can describe my problem in detail tomorrow morning if you guys are interested. Maybe you can see an alternative solution that i cant. I know there are lots of different ways that leads to rome, but in my case this is the only option i have come up with so far that will preform with acceptable speeds.
But i'll come back with more info tomorrow.
January 4, 2012 at 6:31 pm
erikpoker (1/4/2012)
Hello,Im selecting values from a table a:
SELECT a.col1, a.col2, a.col3, a.foreignkey-valueFROM mytable a
WHERE something=something
If the foreign-key-value is null i would like to insert it into the parent table on the fly. I thought i could use a function for this, but apparantly this is not the case since functions wont accept inserts and functions cant call a sp.
One way would be to first select all null values and then do insert of them. And then do my select. But i cant do it this way. Why you might ask? Well its really a bit complicated to explain, so if you could please just try and help me to find a way to do it within the select query i would really appriciate it.
Thanks for your help!
This is sort of like asking for help representing the number 1 because you're not allowed to use the number 1 but you can't say why. As others have said, if you are working under some arbitrary restriction, it's very difficult to help you, especially when you won't clearly state what the restriction is.
What you are asking is extremely trivial with any rational use of SQL Server and I cannot imagine the conditions under which you work if you are restricted to simple select statements at the same time you are somehow allowed to create functions and make openrowset calls. Why would you be able to do these things to implement a dangerous solution but you can't create a parameterized stored procedure that would operate much more safely and have better performance? I hope you understand that from the same stored procedure (or ad-hoc t-sql batch) you can both update the parent table and return the data you want.
January 5, 2012 at 12:28 pm
Erik,
I would be very interested to see the code for the getForeignKey function. I've never researched or tried to use OPENROWSET in a function. My mind doesn't go that way. I would never just willy nilly insert some arbitrary row to a parent table on the fly just so an insert with a missing FK could be satisfied. Not proper management of data. The FK is there for a reason - to enforce relational integrity.
I designed a system where 90% of the database is loaded from data provided by a satellite TV provider and it sometimes has new data codes come in with the data. They don't provide any sort of master codes table download and aren't likely to in the future. The codes are necessary for some processing that gets done with the data after loading. I don't have an FK on this relationship because I can't control what comes in from the download and I'm not going to abort a massive data load just because we are missing a code. I go ahead and insert the downloaded data.
However, no processing is allowed if there is a missing code in the the parent code table. The users have to call up the provider to get an explanation of the code and then enter the code into the parent code table in order to do any processing. (Kind of enforcing referential integrity by phone.)
The point here is you either have DRI or you don't in your data. If you need an FK relationship then you need it and jumping through hoops to insert missing parent records on the fly is probably not a good idea. If you don't really need the FK relationship, then don't declare one. Find another way to fill in the missing blanks and prevent further processing until that is done.
Todd Fifield
June 12, 2012 at 7:52 am
Hi,
I have the same problematic.
Could you share your stored proc using the openrowset ?
Many tks.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply