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