November 1, 2016 at 9:48 am
Wasn't sure how to word the title 🙂
I'm working on a method to take user input to update a (target) table named UDFTemp. With some help in a different thread I think I have merge working when I use the same table structures to do the input form a source table (UDFIn)
I'd like to see if there is a straightforward way I could do it with a different source structure.
The UDFTemp target table is a 4 column table with Unique Index Field (JobNumber), and 3 data fields UDF1, UDF2, and UDF3.
The UDFIn source table would be 3 columns with the match/key field, the name of the data field to update and the data to put in the field:
e.g.
JobNumber, UDFName, and UDF Value.
12345, UDF1, Dogs
12345, UDF2, Fish
12346, UDF1, Cats
12346, UDF2, NULL
Can anyone show me how I could update the target from such a source? I'm stuck with the translation from UDF1 data field to column name. Thought of using a case but got lost 🙁
November 1, 2016 at 10:51 am
1) Please post up table defs, sample data (in the form of INSERTS) and expected results to clarify your logic and help us write queries to solve.
2) Your names make me thing User Defined Function Tables. BAD if so!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2016 at 11:54 am
I tend to steer clear of the MERGE statement.
Below is a sample of doing it as separate INSERT/UPDATE statements.
--DROP TABLE #UDFTemp;
CREATE TABLE #UDFTemp (
JobNumber INT NOT NULL PRIMARY KEY CLUSTERED
, UDF1 VARCHAR(20) NULL
, UDF2 VARCHAR(20) NULL
, UDF3 VARCHAR(20) NULL
);
INSERT INTO #UDFTemp ( JobNumber, UDF1, UDF2, UDF3 )
VALUES ( 12345, 'Rats', NULL, 'Mice' );
--DROP TABLE #UDFIn;
CREATE TABLE #UDFIn (
JobNumber INT NOT NULL
, UDFName SYSNAME NOT NULL
, UDFValue VARCHAR(20) NULL
, PRIMARY KEY CLUSTERED (JobNumber, UDFName)
);
INSERT INTO #UDFIn ( JobNumber, UDFName, UDFValue )
VALUES
( 12345, 'UDF1', 'Dogs' )
, ( 12345, 'UDF2', 'Fish' )
, ( 12346, 'UDF1', 'Cats' )
, ( 12346, 'UDF2', NULL );
--DROP TABLE #Inserted;
CREATE TABLE #Inserted (
JobNumber INT NOT NULL PRIMARY KEY CLUSTERED
);
SELECT * FROM #UDFTemp;
WITH cteData AS (
SELECT
src.JobNumber
, UDF1 = MAX(CASE WHEN src.UDFName = 'UDF1' THEN src.UDFValue ELSE NULL END)
, UDF2 = MAX(CASE WHEN src.UDFName = 'UDF2' THEN src.UDFValue ELSE NULL END)
, UDF3 = MAX(CASE WHEN src.UDFName = 'UDF3' THEN src.UDFValue ELSE NULL END)
FROM #UDFIn AS src
LEFT JOIN #UDFTemp AS dst ON src.JobNumber = dst.JobNumber
WHERE dst.JobNumber IS NULL
GROUP BY src.JobNumber
)
INSERT INTO #UDFTemp ( JobNumber, UDF1, UDF2, UDF3 )
OUTPUT INSERTED.JobNumber
INTO #Inserted ( JobNumber )
SELECT JobNumber, UDF1, UDF2, UDF3
FROM cteData;
WITH cteData AS (
SELECT
src.JobNumber
, UDF1 = MAX(CASE WHEN src.UDFName = 'UDF1' THEN src.UDFValue ELSE NULL END)
, UDF2 = MAX(CASE WHEN src.UDFName = 'UDF2' THEN src.UDFValue ELSE NULL END)
, UDF3 = MAX(CASE WHEN src.UDFName = 'UDF3' THEN src.UDFValue ELSE NULL END)
FROM #UDFIn AS src
LEFT JOIN #Inserted AS i ON src.JobNumber = i.JobNumber
WHERE i.JobNumber IS NULL
GROUP BY src.JobNumber
)
UPDATE #UDFTemp
SET UDF1 = ISNULL(cte.UDF1, dst.UDF1)
, UDF2 = ISNULL(cte.UDF2, dst.UDF2)
, UDF3 = ISNULL(cte.UDF3, dst.UDF3)
FROM cteData AS cte
INNER JOIN #UDFTemp AS dst ON cte.JobNumber = dst.JobNumber;
SELECT * FROM #UDFTemp;
November 1, 2016 at 12:31 pm
Great, thank you. Dissecting and learning now.
The UDF is just a user defined field. A text string they can give me to tie into a different data source that gets updated daily.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply