inserting from within a function. can it be done ?
-
The problem:
I need to fill in some history records for a number of related 'accounts' and set off merrily writing a function to initially build and return a string representing the number of records i had to create for that account.
so i had a table like this
table a
accid start span
1 2 4
2 3 6
3 4 7
and running
select accid, fn_build(start, span) from table a
would return
1, '3,4,5,6'
2, '4,5,6,7,8,9'
3, '5,6,7,8,9,10,11'
all was well in the world.
so i started to amend the function to insert the required number of records and found out that you cannot have an insert statement (into a real table) within a function.
trying to be clever i created some dynamic statements and found out you can't EXEC from within a function either.
and i can't replace the function with a stored procedure as i can't call the sproc from and sql statement.
one of the articles on the net suggested creating a linked server that is linked to itself and then using openquery ...
does anytone know of any way to do this ?
cheers
dbgeezer
-
It's not entirely clear what you're trying to do. Why is data in the form:
3, '5,6,7,8,9,10,11'
Wouldn't you have to parse that up again ?
-
Here's a little code to show how to insert into a function... however I don't think it would help in your case. Why are you trying to insert records in the function?... Shouldn't you update the base table and then requery the function to get an updated result? Couldn't this be done on the client side just by sending the start and span columns?
CREATE TABLE TEST
(
id int not null identity(1,1) primary key,
name varchar(100) not null)
GO
CREATE FUNCTION dbo.fnTESTInsert ()
RETURNS TABLE
AS
RETURN SELECT id, NAME from dbo.TEST
GO
Insert into dbo.fnTESTInsert() (NAME) SELECT 'TEST' UNION ALL SELECT 'TEST2'
Select * from dbo.TEST
DROP FUNCTION fnTESTInsert
DROP TABLE test
-
this was only a test harness of kinds so i could be sure that i was generating the right sequences for the inserts with each insert having and incrementing value.
cheers
dbgeezer
-
I haven't tried your code yet.
What I need to do is iterate over possible millions of records that have associated history records that have a sequence 1.. n
I need to generate, for each record, the corresponding missing history records with the correct sequence values.
I was planning on using the function to do the inserts as I could call it from a select of the original account table and hence do it in a set based manner.
The problem I have relates to any workaround to doing an insert from within a function, there is no need to worry yourself about the logic.
cheers
dbgeezer
-
It just can't be done. You can't update or insert data from INSIDE a function.
So you are basically searching for gaps in an identity environement, identity which is reseeded on each object of the history?
-
yeah kind of
each account has a number of history records and so i need to 'back fill' the missing history records for each account.
i've rewritten the query to returna table representing all the rows i need to insert into the history table but now need to call this for each row in the account table ...
swings and roundabouts 😉
cheers
dbgeezer
-
It seems like you'd do well to just use an intermediate table to hold the results of these missing records. I take it this is some kind of ad hoc, one-time cleanup, not a production sort of thing (Please tell me that's true).
I.e., Step 1: populate a "holding" table with missing records
Step 2: load missing records back into the history table with an INSERT statement using data from the "holding" table
-
I guess working at 2 AM on a "very large system designed by someone else" can really put a person on edge.
I guess I'll stay out of this thread from now on. Best of luck in getting your "insert from within a function" approach to work. You're breaking new ground here.
-
Apologies and I've deleted the original reply
I'm in the UK so the reply was made this morning.
It is a big system, poorly designed and a complete headache most of the time. It is frustrating though as working with TSQL is such a ball ache. I will crack it though but may have to use a cursor.
Apologies again
cheers
dbgeezer
-
>>I need to generate, for each record, the corresponding missing history records with the correct sequence values.<<
the way to do that is to CROSS JOIN your table with all possible pk values and filter out those that are already present. I have done this multiple times with history tables, but I have to admit that in my cases it was always easy because the fill-ins were either dates or sequencial integer values, may be its harder in yours ?
hth
* Noel -
the solution i used was to:
1. identify all the records i needed to change and compute a span value of the history records missing
2. cylce through these using a cursor to call a function that returns a table detialing the missing records. these records are then inserted into the history table.
we're not expecting millions of these histories to be created and 6.5k sets of 72 record histories took only a minute to run so the use of a cursor isn't an issue either.
cheers
dbgeezer
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply