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.
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#" />
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.)
[Add Comment]
[Subscribe to Comments]
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
Sure, that would be awesome!
# Posted By Ryan Vikander
| 5/1/14 12:27 PM
[Add Comment]
