Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is there a smarter way than a cursor for this? Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 9:31 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 8:27 AM
Points: 24, Visits: 133
Hi all,

I'm migrating some data due to software improvements. We have about 25k surveys, each with around 3 questions on them. The migration will run as a one off during a release when the systems are closed and a couple of hours duration for this is acceptable.

What I need to do is for each survey, copy the questions for it into the same questions table. So, dbo.Question has some questions in it and each one is going to be copied into the same table - dbo.Question. Each original question is then going to have a field updated so it points to the new question.

Ok, so that probably sounds completely bananas! So let me explain a bit about what is going on. This is a huge and complex system and the admin users for the questions are having daily hell in working with it. They run "campaigns" for clients, which are basically collections of surveys. So a project has been agreed to make their life a whole lot simpler. Trouble is, we have to draw the line somewhere to be able to release this year, and so we are having to compromise somewhat to avoid a huge amount of regression work.

So, that's left us with:
* leave each question on its survey so all the web pages work
* copy the question up to the "campaign level" for the new software
* point the original question at its new "campaign level" copy so the new software can keep tabs

I would use SELECT INTO to copy the questions but the bit I'm stuck on is how to get the existing questions pointing to their "campaign level" equivalents. I can solve this by iterating through a question at a time (i.e. cursor) but I was wondering if there was a smarter, set-based way?

Sorry this is horrific. We would all like to scrub this and do it properly. But this is the real world. And we get paid for making things work.

dbo.Question
- QuestionId INT NOT NULL IDENTITY(1, 1)
- ParentQuestionId INT NULL
- IsForCampaign BIT NOT NULL DEFAULT (0)

When the migration inserts a question, ParentQuestionId is null, and IsForCampaign is 1. The original question is then updated so that its ParentQuestionId is set to the QuestionId of the new record.
Post #1385245
Posted Thursday, November 15, 2012 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
This doesn't sound too bad but there is really nothing anybody can do to help based on the limited details you have posted. Are you looking for specific query assistance or just general guidelines on how to do this? Either way you need to help us understand the problem. The easiest way for that is to post ddl, sample data, and desired output based on the sample data. See the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385252
Posted Thursday, November 15, 2012 9:50 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 8:27 AM
Points: 24, Visits: 133
Yes, I see what you mean. Let me see if I can clarify:-

I guess the question I need to answer is:- if I'm going to insert a batch of records and I need to get to the primary key values of those records, have I any choice but to do them one at a time?
Post #1385255
Posted Thursday, November 15, 2012 10:22 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 8:27 AM
Points: 24, Visits: 133
Here you go ... hope this helps ...

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Question','U') IS NOT NULL
DROP TABLE #Question

--===== Create the test table with
CREATE TABLE #Question
(
QuestionID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SurveyID INT NULL -- Foreign key to the survey (not shown here)
ParentQuestionId INT NULL,
QuestionText VARCHAR(255) NULL,
CampaignId INT NULL -- Foreign key to the campaign (not shown here)
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Question ON

--===== Insert the test data into the test table
INSERT INTO #Question
(QuestionID, SurveyID, ParentQuestionId, QuestionText)
SELECT 1, 1, NULL, 'What is your current system?' UNION ALL
SELECT 2, 1, NULL, 'How old is it' UNION ALL
SELECT 3, 2, NULL, 'How much do you spend each week?' UNION ALL
SELECT 4, 2, NULL, 'Do you use budgeting software?' UNION ALL
SELECT 5, 2, NULL, 'What do you use at the moment?' UNION ALL
SELECT 6, 3, NULL, 'Do you run AV software?'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Question ON

Expected result below, after migration. This shows the original questions, along with the new copies of those questions. The original questions have been updated with the QuestionIDs of the new questions.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Question','U') IS NOT NULL
DROP TABLE #Question

--===== Create the test table with
CREATE TABLE #Question
(
QuestionID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SurveyID INT NULL -- Foreign key to the survey (not shown here)
ParentQuestionId INT NULL,
QuestionText VARCHAR(255) NULL,
CampaignId INT NULL -- Foreign key to the campaign (not shown here)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Question ON

--===== Insert the test data into the test table
INSERT INTO #Question
(QuestionID, SurveyID, ParentQuestionId, QuestionText, CampaignId)
SELECT 1, 1, 7, 'What is your current system?', NULL UNION ALL
SELECT 2, 1, 8, 'How old is it', NULL UNION ALL
SELECT 3, 2, 9, 'How much do you spend each week?', NULL UNION ALL
SELECT 4, 2, 10, 'Do you use budgeting software?', NULL UNION ALL
SELECT 5, 2, 11, 'What do you use at the moment?', NULL UNION ALL
SELECT 6, 3, 12, 'Do you run AV software?', NULL UNION ALL
SELECT 7, NULL, NULL, 'What is your current system?', 21 UNION ALL
SELECT 8, NULL, NULL, 'How old is it', 21 UNION ALL
SELECT 9, NULL, NULL, 'How much do you spend each week?', 22 UNION ALL
SELECT 10, NULL, NULL, 'Do you use budgeting software?', 22 UNION ALL
SELECT 11, NULL, NULL, 'What do you use at the moment?', 22 UNION ALL
SELECT 12, NULL, NULL, 'Do you run AV software?', 23

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Question ON

Post #1385267
Posted Thursday, November 15, 2012 10:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 36,995, Visits: 31,514
I guess I don't get this. Why would the ParentID of question 1 be "7" when it looks like question 7 is the identical question with no ParentID? Are you trying to build a "forrest" of questions with each having a "downline" of follup question? If so, what is the purpose of pointing row 2 to row 8?

Just trying to understand what you need.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385272
Posted Thursday, November 15, 2012 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
Jeff Moden (11/15/2012)
I guess I don't get this. Why would the ParentID of question 1 be "7" when it looks like question 7 is the identical question with no ParentID? Are you trying to build a "forrest" of questions with each having a "downline" of follup question? If so, what is the purpose of pointing row 2 to row 8?

Just trying to understand what you need.


I am pretty sure I get what he is after Jeff. I think he is just trying to add these questions to an existing table and wants to maintain the relationship between survey and questions. So SurveyID 1 in the new table becomes SurveyID 42, need to get the questions to have the new ID of 42. I have an example I am working on for this but I am going to lunch so feel free to beat me to to it if you want.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385275
Posted Thursday, November 15, 2012 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
Assuming my understanding posted above is correct here is an example of doing this type of thing without using a cursor. What you have to do is add a column in the destination tables to hold the current PK values. Then you can use those values and update accordingly. I hobbled together a simplified version based on your problem. See if this is doing what you are trying to do.

create table #LegacySurvey
(
SurveyID int identity,
SurveyName varchar(10)
)

create table #LegacySurveyQuestion
(
SurveyQuestionID int identity,
SurveyID int,
Question varchar(50)
)

insert #LegacySurvey
select 'Survey 1' union all
select 'Survey 2'

insert #LegacySurveyQuestion
select 1, 'Question 1' union all
select 1, 'Question 2' union all
select 2, 'Question 1' union all
select 2, 'Question 2'

create table #NewSurvey
(
SurveyID int identity,
SurveyName varchar(50)
)

create table #NewSurveyQuestion
(
SurveyQuestionID int identity,
SurveyID int,
Question varchar(50)
)

Insert #NewSurvey
select 'This one is already taken' union all
select 'This ons is also already taken'

insert #NewSurveyQuestion
select 1, 'New Question 1' union all
select 1, 'New Question 2' union all
select 2, 'New Question 1' union all
select 2, 'New Question 2'

--The above is intended to represent the new destination table that is already populated with some data.
--Everything above here is setting up your current situation. You have a source table and a destination table
--both populated with some rows and you need to move the Survey and Questions to the new table and be able to
--keep the existing relational key intact but with new values.

--The easiest way to handle this is to add Legacy Columns to the new table.
Alter table #NewSurvey
add LegacySurveyID int --This lets us know the SurveyID we came from

Alter table #NewSurveyQuestion
add LegacySurveyID int --This lets us know the SurveyID we came from

--We need to populate the LegacySurveyID with the current SurveyID
insert #NewSurvey (SurveyName, LegacySurveyID)
select SurveyName, SurveyID from #LegacySurvey

--We need to populate the LegacySurveyID with the current SurveyID
insert #NewSurveyQuestion(Question, LegacySurveyID)
select Question, SurveyID from #LegacySurveyQuestion

--Now we just change the value of SurveyID using the legacy values from both tables
update #NewSurveyQuestion
set SurveyID = s.SurveyID
from #NewSurveyQuestion q
join #NewSurvey s on q.LegacySurveyID = s.LegacySurveyID

--Next we drop the temporary columns used to hold our mapping
alter table #NewSurvey
drop column LegacySurveyID

alter table #NewSurveyQuestion
drop column LegacySurveyID

--This is the old data to use as comparison for the new versions below
Select *
from #LegacySurvey s
join #LegacySurveyQuestion q on q.SurveyID = s.SurveyID

--If all worked as expected we should see the previously existing surveys PLUS the newly inserted ones.
--They should all be matched correctly to the right parent.
select *
from #NewSurvey s
join #NewSurveyQuestion q on q.SurveyID = s.SurveyID

drop table #LegacySurvey
drop table #LegacySurveyQuestion
drop table #NewSurvey
drop table #NewSurveyQuestion




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385311
Posted Thursday, November 15, 2012 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
Reading your post again it sounds like you want to make copies of a survey? You could use the same logic I posted above but it would all be within the same table. The same exact concept will work.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385314
Posted Thursday, November 15, 2012 12:34 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 8:27 AM
Points: 24, Visits: 133
Ok, great. Let me take a look at your solution ...
Post #1385315
Posted Thursday, November 15, 2012 1:33 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 8:27 AM
Points: 24, Visits: 133
Thanks very much for your help so far!

I'm at home now so I haven't got access to SSMS, but I've read through your code and I think I can see what's going on. The bit I'm unsure about is you have new and legacy tables with data going between them. This could be just to illustrate your point, which is fine in that case.

But let me just focus down on to the crux of the problem. I've ommitted all the other fields - just the ones to to with the IDs are left.

Table before migration
QuestionID ParentQuestionId
---------- ----------------
1 NULL

Table after migration
QuestionID ParentQuestionId
---------- ----------------
1 7
7 NULL

So what the migration has done is:-

(a) Insert a new record (this happens to be a copy of #1)
(b) Updated #1 to point to #7

And what I'm trying to find out on this forum is:-

(*) Will I have to cursor through the table, and process each record ... or is their an alternative?

This is awful, repeated, redundant data, that's for sure. Sadly the database is being a bit of a victim here to allow the software changes to go through without us having many weeks of Developer time to do it properly. We're having to do it this way to limit that amount of the system that needs code changes and testing.
Post #1385343
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse