June 12, 2012 at 7:03 am
Yes I know that's not possible for very good reason.
Now let's explain to you my issue.
I have a input table containing x records.
One one the column contains a formatted string like abc|def|hijkl|zwqa|ouity (5 <> items).
I want to use the merge function to update a destination table which contains the 5 items into 3 differents columns.
item 1 & item3
item 2
item 4 & item 5
Of course I store not the items but the ID of the items stored in other tables.
Till now, no problem. A function can handle this easily.
Now Item 4 & Item 5 could sometime not exist yet in their reference table. If they don't exist yet records need to be created and ID retrieved.
To create the records, I use INSERT and here my problem !!!
In the merge statement, I can use functions to get the Id back but seems I can not use stored proc...
Merge Using ... On.... When .... Insert (....) Values (...)
How to solve this ?
Tks for your help.
June 12, 2012 at 7:13 am
Are you attempting to update/insert two different tables within one merge statement? It's possible to do this with composible DML, but there's not enough information in your post to determine if this is going to work for you. Can you post DDL and some sample data?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2012 at 7:19 am
Tks for your quick reaction.
To explain you
Input table : several columns + 1 column containing 5 <> col (string based) in 1 (separated by |).
Output table : same several columns + 3 columns (INT Primary Key to 3 tables containing the <> string items.
Sometimes the Primary Key do not yet exist in the reference table and therefore need to be created 'on the fly' during the merge process.
Logic is OK as far as Primary KEy exist.
Problem is where I need to create the records in the references table (which is not the Output table).
Is it more clear ?
I have heard that openrowset could be the solution....
June 12, 2012 at 7:25 am
francois.vandecan (6/12/2012)
Tks for your quick reaction.To explain you
Input table : several columns + 1 column containing 5 <> col (string based) in 1 (separated by |).
Output table : same several columns + 3 columns (INT Primary Key to 3 tables containing the <> string items.
Sometimes the Primary Key do not yet exist in the reference table and therefore need to be created 'on the fly' during the merge process.
Logic is OK as far as Primary KEy exist.
Problem is where I need to create the records in the references table (which is not the Output table).
Is it more clear ?
I have heard that openrowset could be the solution....
Up to 4 tables to update/insert? I'd do INSERTs to the 3 reference tables first, then the output table using MERGE. What would you want to use OPENROWSET for? What advantage would it offer over a transaction?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2012 at 7:28 am
The things is that i's likely not often that I will have to add records to the references tables....
so I would like to have all logic (Get primary keys and create new records if necessary in the same function...)
June 12, 2012 at 7:42 am
Look this is the 'theoretical function'.
ALTER FUNCTION [dbo].[fctProcessSCP]
(
-- Add the parameters for the function here
@SCP varchar(200),
@Separator char(1),
@ProcessId smallInt
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
Declare @SubSCPId int
Declare @SubSCP1Id int
Declare @SubSCP varchar(50)
Declare @RowId int
DECLARE @Result int
-- Add the T-SQL statements to compute the return value here
-- Search for Component and SubComponent
----------------------------------------
If @ProcessId = 1
begin
Set @SubScpId = (Select Id from dbo.Components where LabelText = dbo.fctSplitSCP(@SCP,''|'',1))
Set @SubScp1Id = (Select Id from dbo.SubComponents where LabelText = dbo.fctSplitSCP(@SCP,''|'',3))
Set @Result = (Select Id from ComponentsAndSubComponents where ComponentId = @SubScpId and SubComponentId = @SubScp1Id)
end
-- Search for CustomerEntityId
If @ProcessId = 2
Set @Result = (Select ID From dbo.Customers Where LabelText = dbo.fctSplitSCP(@SCP,''|'',2))
If @ProcessId = 3
begin
-- Search for Category
Set @SubSCP = dbo.fctSplitSCP(@SCP,''|'',4)
If @SubSCP is null
Set @SubScpId = (Select Id from dbo.Categories where LabelText is null)
Else
Set @SubScpId = (Select Id from dbo.Categories where LabelText = @SubSCP)
If @SubScpId is null
Exec dbo.CreateCatgId @SubSCP,@SubScpId
-- Search for SubCategory
-------------------------
Set @SubSCP = dbo.fctSplitSCP(@SCP,''|'',5)
If @SubSCP is null
Set @SubScp1Id = (Select Id from dbo.SubCategories where LabelText is null)
Else
Set @SubScp1Id = (Select Id from dbo.SubCategories where LabelText = @SubSCP)
-- Check if Catg is present
If @SubScpId = 0
exec dbo.CreateSubCatgId @SubSCP,@SubSCP1ID
Set @Result = (Select Id from CatgAndSubCatg where CategoryId = @SubScpId and SubCategoryId = @SubScp1Id)
If @Result = 0
exec dbo.CreateCatgIdAndSubCatgId @SubScpId,@SubScp1Id,@Result end
-- Return the result of the function
RETURN @Result
Of course calling the stored proc inside the function is not allowed....
So what's the solution ?
June 12, 2012 at 7:53 am
Use a stored procedure instead of a function.
Functions like that (data-accessing scalar functions) are terrible for performance
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 7:55 am
Tks but ...
I'm using the result of the function inside a merge statement... Therefore function is mandatory.....
June 12, 2012 at 8:00 am
Then you need to do some re-architecting. SP calls are not allowed in a function, data modifications are not allowed in a function, a function like that in a merge will make the merge many times slower than it needs to be.
And before anyone suggests the openrowset 'trick', that's extremely risky as you could easily have your procedures called hundreds of times, or once, or anything in between. The reason that data modifications aren't allowed in a function is so that the optimiser has freedom in choosing how many times to execute the function. It might work, right up to the point it hoses your data, causes deadlocks, major blocking or other such amusements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 8:40 am
francois.vandecan (6/12/2012)
Tks but ...I'm using the result of the function inside a merge statement... Therefore function is mandatory.....
Function isn't mandatory. Don't try it. Follow Gail's advice and do the updates properly.
For each of the three lookup tables, insert new rows from source; then merge source with target. If you need help with this, then post DDL and DML.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply