Is there a smarter way than a cursor for this?

Question

Is there a smarter way than a cursor for this?

Alex-815008

Mr or Mrs. 500

Points: 510
More actions
November 15, 2012 at 9:32 am

#270306

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.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 2

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?

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 8

Alex-815008

Mr or Mrs. 500

Points: 510

November 15, 2012 at 12:34 pm

#1559492

Ok, great. Let me take a look at your solution ...

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 9

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.

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

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

Yes the alternative is what I showed you. You should be able to easily tweak my example and use a single table instead of two.

You would just be inserting into Survey from Survey. I modified my example to simply copy ALL of the existing surveys and their associated questions.

Same exact concept.

create table #Survey

(

SurveyID int identity,

SurveyName varchar(50)

)

create table #SurveyQuestion

(

SurveyQuestionID int identity,

SurveyID int,

Question varchar(50)

)

Insert #Survey

select 'This is the first one to copy' union all

select 'This is number two'

insert #SurveyQuestion

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 survey question prior to copying the data.

--Now is where your new code would start.

--We want to copy all of the existing surveys

--The easiest way to handle this is to add Legacy Columns to the new table.

Alter table #Survey

add LegacySurveyID int --This lets us know the SurveyID we came from

Alter table #SurveyQuestion

add LegacySurveyID int --This lets us know the SurveyID we came from

--We need to populate the LegacySurveyID with the current SurveyID

insert #Survey (SurveyName, LegacySurveyID)

select SurveyName, SurveyID from #Survey

--We need to populate the LegacySurveyID with the current SurveyID

insert #SurveyQuestion(Question, LegacySurveyID)

select Question, SurveyID from #SurveyQuestion

--Now we just change the value of SurveyID using the legacy values from both tables

update #SurveyQuestion

set SurveyID = s.SurveyID

from #SurveyQuestion q

join #Survey s on q.LegacySurveyID = s.LegacySurveyID

--Next we drop the temporary columns used to hold our mapping

alter table #Survey

drop column LegacySurveyID

alter table #SurveyQuestion

drop column LegacySurveyID

--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 #Survey s

join #SurveyQuestion q on q.SurveyID = s.SurveyID

drop table #Survey

drop table #SurveyQuestion

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 11

ok, thanks for the clarification.

I'll try this at work tomorrow.

Alex-815008 Mr or Mrs. 500 Points: 510 More actions · Answer 12

That was a smart solution Sean.

I've implemented this approach in the script now. Thanks for much 🙂

Sean Lange SSC Guru Points: 286573 More actions · Answer 13

Alex-815008 (11/19/2012)
That was a smart solution Sean.
I've implemented this approach in the script now. Thanks for much 🙂

You are quite welcome. Glad that worked for you and thanks for letting me know.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/