January 18, 2012 at 7:45 am
I am struggling with how best to do a data update to a table.
I have (right now) a comma separated string of numbers which need to go in a table.
The table is QuestionID, StakeholderID, AssignmentID.
The comma separated values are all stakeholderids.
If there is an entry in the table with that questionid and stakeholderid, do nothing.
If there isn't an entry in the table, insert it.
AND, if there is an entry in the table with that questionid and stakeholderid but that stakeholderid is not in the comma separated string, then delete it from the table.
I have no code right now because I am trying to do this in the best possible way and can't seem to get a foothold in this. I'm a good basic SQL programmer but need more help with set based updates.
January 18, 2012 at 7:53 am
Check out Jeff Moden's splitter article for the best way to split your comma separated string --> http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D.
Once you've implemented that, you can split the strings into a data-set (temp table or subquery) and run your UPDATE/INSERT/DELETE logic from that.
(Provide readily consumable sample data and DDL scripts if you want more help than that 😉 )
January 18, 2012 at 7:53 am
Focus on resolving the string into a temp table first, the rest is straightforward.
Can you provide us with more details about this string?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 7:55 am
What do you want to know about this string? I can put that into any format you want through VB.net.
It's a list of selected stakeholderids from a webform.
A temporary table...hmm. Do I need to worry about performance doing that?
January 18, 2012 at 8:00 am
Laurie-1124340 (1/18/2012)
What do you want to know about this string? I can put that into any format you want through VB.net.It's a list of selected stakeholderids from a webform.
A temporary table...hmm. Do I need to worry about performance doing that?
Hi Laurie, it's about tying down requirements mostly - "If there is an entry in the table with that questionid and stakeholderid, do nothing" - does the string consist only of stakeholders, or are there questionID's too?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 8:08 am
The string contains ONLY the stakeholderids. There will be ONE QuestionID to update so that stays the same for all stakeholders.
QuestionID StakeholderID
1 10
1 9
2 10
1 2
This is literally it for right now (but this will become large once the app is launched)
So, I would be giving a string like this:
10,9,14 for question 1.
I would want nothing to happen to the first or second entry in the table.
I would want a new entry for stakeholder 14 on question1
and I would want the entry for stakeholder2 deleted from this table.
Is the best way to go with a temp table?
January 18, 2012 at 8:38 am
Laurie-1124340 (1/18/2012)
The string contains ONLY the stakeholderids. There will be ONE QuestionID to update so that stays the same for all stakeholders.QuestionID StakeholderID
1 10
1 9
2 10
1 2
This is literally it for right now (but this will become large once the app is launched)
So, I would be giving a string like this:
10,9,14 for question 1.
I would want nothing to happen to the first or second entry in the table.
I would want a new entry for stakeholder 14 on question1
and I would want the entry for stakeholder2 deleted from this table.
Is the best way to go with a temp table?
You need to add something that acts as the order of that table. SQL Server doesn't guarantee ordering, so although physically you can see that 2 is the last record for question 1, SQL Server doesn't know that.
Couple of options (there are more, just here are a couple) - Could add an IDENTITY column, or a DATETIME column with a default of GETDATE().
January 18, 2012 at 8:40 am
There is an identity column already called AssignmentID but I am not sure how that helps. Like I said, still learning....and I have a LOT to learn
January 18, 2012 at 9:22 am
Here's my guess: -
BEGIN TRAN
CREATE TABLE #yourSampleData (AssignmentID INT IDENTITY, QuestionID INT, StakeholderID INT)
INSERT INTO #yourSampleData
SELECT * FROM (VALUES(1, 10),(1, 9),(2, 10),(1, 2))a(QuestionID,StakeholderID)
--Set string and question number
DECLARE @string VARCHAR(4000) = '10,9,14', @question INT = 1
--Split the string, I'm using XML to do it because it's easier for me to
--post as a readily consumable script for you. However, please test the
--performance against Jeff Moden's splitter which can be found
--here --> http://www.sqlservercentral.com/articles/Tally+Table/72993/
DECLARE @xml XML
SET @XML = N'<root><r i="' + REPLACE(@string,',', '" /><r i ="') + '" /></root>'
IF object_id('tempdb..#splitString') IS NOT NULL
BEGIN
DROP TABLE #splitString
END
--Insert split string into a temporary table
CREATE TABLE #splitString (orderingID INT IDENTITY, StakeholderID INT, QuestionID INT)
INSERT INTO #splitString
SELECT StakeholderID, QuestionID
FROM (SELECT x.y.value('@i','int'), @question
FROM @XML.nodes('root/r') x(y)) xmlSplit(StakeholderID,QuestionID)
--Delete if missing from string
DELETE FROM #yourSampleData
WHERE AssignmentID IN (SELECT data.AssignmentID
FROM #yourSampleData data
LEFT OUTER JOIN #splitString splitString ON data.QuestionID = splitString.QuestionID AND
data.StakeholderID = splitString.StakeholderID
WHERE data.QuestionID = @question AND splitString.orderingID IS NULL)
--Insert if missing from table
INSERT INTO #yourSampleData
SELECT QuestionID, StakeholderID
FROM #splitString split
WHERE StakeholderID NOT IN (SELECT StakeholderID
FROM #yourSampleData
WHERE QuestionID = @question)
--Check final result
SELECT * FROM #yourSampleData
ROLLBACK
So, starting data:
AssignmentID QuestionID StakeholderID
------------ ----------- -------------
1 1 10
2 1 9
3 2 10
4 1 2
String - '10,9,14'
Question - 1
Final data:
AssignmentID QuestionID StakeholderID
------------ ----------- -------------
1 1 10
2 1 9
3 2 10
5 1 14
Am I on the right track?
January 18, 2012 at 9:25 am
WOW! Yes, I think I can make that work. I need to get better at SQL Server instead of doing everything in VB.net. This project is going to make me become better.
Thanks!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply