Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new table, i.e:
Tables
____
Library_1 -- table for Survey 1
SurveyId int
InstanceId int
Q_1 varchar(50)
Library_2 -- table for Survey 2
SurveyId int
InstanceId int
Q_2 int
Q_3 int
Q_4 varchar(255)
The tables are generated with the SurveyId
at the end of the name (Library_
) and the Question columns are generated with the QuestionId
at the end of it (Q_
). To clarify, the questions are stored in a separate table so while the question ids are sequential they do not start at 1 for each survey. The question columns will be based on the id assigned to them in the table.
Seems simple enough to query, except we need to extract the data from all of the survey tables to be sent to another system and this is where the problem comes in. Since the tables are created automatically when a new survey is added by the front-end application, the other system cannot handle this type of structure. They need the data to be consistent for them to consume.
So I was tasked with writing a stored procedure that will extract the data from all Survey tables and place it in the following format:
SurveyId InstanceId QNumber Response
________ __________ _______ ________
1 1 1 great
1 2 1 the best
2 9 2 10
3 50 50 test
By having the data for all tables in the same format, then it can be consumed by anyone no matter how many survey tables and questions exist.
I wrote a stored procedure that appears to be working but I am wondering if I am missing something or if there is a better way to handle this type of situation.
My code:
declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM SurveyData)
Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''
WHILE @RowCount <= @TotalRecords
BEGIN
SELECT @TableName = tableName, @ColumnName = columnName
FROM SurveyData
WHERE @RowCount = rownum
SET @sql = @sql +
' SELECT s.SurveyId
, s.InstanceId
, CASE WHEN columnName = ''' + @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
, Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
FROM SurveyData t
INNER JOIN ' + @TableName + ' s' +
' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
' WHERE t.columnName = ''' + @ColumnName + ''''
IF @RowCount != @TotalRecords
BEGIN
set @sql = @sql + ' UNION ALL'
END
SET @RowCount = @RowCount + 1
END
exec(@sql)
I have created a SQL Fiddle with some sample data and the code.
Is there a different way this type of query should be written? Are there any noticeable issues with it?
Unfortunately, there are many unknowns with this...how many tables we will have and how many questions per survey. I would say that we will have between 25-50 surveys, with 2-5 questions each.