Hello Everyone,
I have taken the liberty to create SQL Stored Procedures to Export results from the DynamicForms_QuestionResponse table and insert these values into a temp table called “Export_Temptable”. I have also created SQL Stored Procedures to Import the records in the Export_TempTable to a Flat table of my choice.
This blog is broken into 2 parts.
Part 1 covers the Export
Part 2 covers the Import
Part 1 – Export
Step 1.) Start by creating this table on your data base(You can do so by going to Host –> SQL on your DNN Installation):
----EXPORT TEMP TABLE
CREATE TABLE Export_TempTable
(
ExportRecordID int identity(1,1),
QuestionResponse nvarchar(500),
QuestionName nvarchar(500),
QuestionValue nvarchar(500),
ResponseDate datetime
)
Step 2.) Now you will need to create the Export procedure on your data base(You can do so by going to Host –> SQL on your DNN Installation):
-----EXPORT PROCEDURE
CREATE PROCEDURE Export_DynamicForms_Results
(
@ModuleID int
)
AS
BEGIN
DECLARE Cart_Cursor CURSOR FOR
select A.UniqueResponseID ,B.ShortFieldName, A.Response, A.ResponseDateTime from DynamicForms_QuestionResponse A inner join
DynamicForms_Question B on A.DynamicQuestionID = B.DynamicQuestionID where B.ModuleID = @ModuleID and ShortFieldName <> 'HumanCaptcha' order by B.ShortFieldName
OPEN Cart_Cursor
DECLARE @V1 nvarchar(400), @V2 nvarchar(400), @V3 nvarchar(400) , @V4 nvarchar(400)
FETCH NEXT FROM Cart_Cursor INTO @V1, @V2 , @V3, @V4
WHILE @@FETCH_STATUS = 0
BEGIN
If @V2 Is Null
BEGIN
Set @V2 = ''
END
If @V3 Is Null
BEGIN
Set @V3 = ''
END
INSERT INTO Export_TempTable(QuestionResponse, QuestionName, QuestionValue, ResponseDate)
VALUES(@V1, @V2, @V3, @V4)
FETCH NEXT FROM Cart_Cursor INTO @V1, @V2, @V3, @V4
END
CLOSE Cart_Cursor
DEALLOCATE Cart_Cursor
end
Step 3.) Test the Export Procedure
To test the Export procedure, go to a Dynamic Form instance and obtain the ModuleID for that Dynamic Form.
Go to Host –> SQL on your DNN Installation and run this SQL (Example ModuleID = 224):
Exec Export_DynamicForms_Results 224
After executing the Stored Procedure execute the below query:
Select * from Export_TempTable
You should be able to see all or most of your of your exported records.
Part 2 – Import
Step 1.) Create Import Procedure 1
----IMPORT PROCEDURE 1 OF 2
CREATE PROCEDURE Import_DynamicForms_Results
AS
BEGIN
DECLARE @UNIQUE nvarchar(200)
DECLARE @COUNT int
DECLARE Import_Cursor CURSOR FOR
select QuestionResponse from Export_TempTable order by QuestionResponse
OPEN Import_Cursor
DECLARE @V1 nvarchar(400)
FETCH NEXT FROM Import_Cursor INTO @V1
WHILE @@FETCH_STATUS = 0
BEGIN
If @V1 Is Null
BEGIN
Set @V1 = ''
END
set @COUNT = (Select COUNT(*) from YourFlatTableNameGoesHere where ResponseUNID = @V1)
Set @UNIQUE = (Select NEWID())
IF @COUNT = 0
BEGIN
INSERT INTO YourFlatTableNameGoesHere (UniqueCompletionID)
VALUES(@UNIQUE)
UPDATE YourFlatTableNameGoesHere Set responseUNID = @V1
where UniqueCompletionID = @UNIQUE
END
exec Import_DynamicForms_Results_Nested @V1, @UNIQUE
FETCH NEXT FROM Import_Cursor INTO @V1
END
CLOSE Import_Cursor
DEALLOCATE Import_Cursor
end
Step 2 – Create Import Procedure 2:
You will need to modify this procedure the most out of all. For this example I have a flat table that looks like this:
Table Name: YourFlatTableNameGoesHere
Columns: ID, UniqueCompletionID, FirstName, LastName, Email
Please note that you must have a UniqueCompletionID column that contains a unique value whether a GUID string or an identity number.
The below procedure has been conformed to the example table. If you’re table has different columns, then you will need to add to or delete from the section color coded “Green”
----IMPORT PROCEDURE NUMBER 2
CREATE PROCEDURE Import_DynamicForms_Results_Nested
(
@UNID nvarchar(500),
@UNIQUE nvarchar(200)
)
AS
BEGIN
DECLARE Import_Cursor2 CURSOR FOR
select Cast(QuestionName as nvarchar(max)), cast(QuestionValue as nvarchar(max)), ResponseDate from Export_TempTable where QuestionResponse = @UNID
OPEN Import_Cursor2
DECLARE @V1 nvarchar(400), @V2 nvarchar(400), @V3 datetime
FETCH NEXT FROM Import_Cursor2 INTO @V1, @V2, @V3
WHILE @@FETCH_STATUS = 0
BEGIN
If @V2 Is Null
BEGIN
Set @V2 = ''
END
IF @V1 = 'FirstName'
BEGIN
Update YourFlatTableNameGoesHere set FirstName = @V2 where UniqueCompletionID = @UNIQUE
END
IF @V1 = 'LastName'
BEGIN
Update YourFlatTableNameGoesHere set LastName = @V2 where UniqueCompletionID = @UNIQUE
END
IF @V1 = 'Email'
BEGIN
Update YourFlatTableNameGoesHere set Email = @V2 where UniqueCompletionID = @UNIQUE
END
FETCH NEXT FROM Import_Cursor2 INTO @V1, @V2, @V3
END
CLOSE Import_Cursor2
DEALLOCATE Import_Cursor2
END
This concludes the blog post.
Let me know if you have any questions.
Thanks,
Ryan