s
Contact Login Register
h M

Exporting Dynamic Form Results and Importing to flat table.

Author: Chad Nash/Wednesday, April 11, 2012/Categories: In The Flow

Rate this article:
No rating

 

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

Number of views (168409)/Comments (-)

Tags:
blog comments powered by Disqus

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

Subscribe