Quick Data Scripter

Posting this just in case I lose my file but I created a CFM page that scripts data for quick insert statements. I know there are good SQL Server Management tools that does this but I wanted something that didn't require trial versions or paid versions.

This assumes your primary key is called "id" but you can change it to be whatever and will output a txt file to a location of your desire.

view plain print about
1<cfset tables = "Table_Name" />
2<cfparam name="key" default="" />
3
4<cfsavecontent variable="script">
5    <cfloop list="#tables#" index="table_name">
6        <cfquery name="getColumns" datasource="dsn">
7            select '[' + c.name + ']' as column_name, c.name, t.name as column_type
8            from sys.columns c
9            left join sys.types t on t.system_type_id = c.system_type_id
10            left join sys.objects o on o.object_Id = c.object_Id
11            where o.name = '#table_name#'
12        </cfquery>
13
14        <cfquery name="getData" datasource="dsn">
15            select #valueList(getColumns.column_name)#
16            from #table_name#
17            <cfif listFindNoCase(valueList(getColumns.column_name), 'active')>
18                where active = 1
19            </cfif>
20            <cfif key neq "">
21                and id = '#key#'
22            </cfif>
23        </cfquery>
24<cfoutput>
25------------------
26<cfloop query="getData">
27    <cfset current_Row = currentRow />
28IF NOT EXISTS (SELECT * FROM #table_name# WHERE id = '#getData.id#')
29BEGIN
30    INSERT INTO #table_name# (#valueList(getColumns.column_name)#)
31    VALUES (<cfloop query="getColumns"><cfif currentRow gt 1>,</cfif><cfif getData[getColumns.name][current_row] neq ''>'#getData[getColumns.name][current_row]#'<cfelse>NULL</cfif></cfloop>)
32END
33</cfloop>
34</cfoutput>
35<cfif listFind(tables, table_name) eq listLen(tables)>------------------</cfif>
36</cfloop>
37</cfsavecontent>
38
39<cffile action="write" file="c:\workspace\script.txt" output="#script#" />

TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Ryan Guill's Gravatar I have an application that ive been working on for years that does this sort of thing - I am actually rewriting it from scratch in hopes to open source it. Would you be interested in beta testing it?
# Posted By Ryan Guill | 5/1/14 9:21 AM
Ryan Vikander's Gravatar Sure, that would be awesome!
# Posted By Ryan Vikander | 5/1/14 12:27 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.6.004. Contact Blog Owner