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 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply