Exporting Data from a SQL Query

1          Write a SELECT statement in the Query window, right-click the query, and then click Generate INSERT Scripts, depicted as follows:

The SELECT statement can be a simple query fetching data from one table, or it can have multiple JOINS combining several tables with complicated WHERE clause.

Notice that the only difference when exporting the result of a query verses exporting from a table is that in the latter case WinSQL Professional generates the SQL statement for you. This statement is a simple SELECT statement fetching all columns from the specified table. Therefore, the screens look very similar in both cases

Note: Synametrics strongly recommends running the query and checking for syntax errors prior to invoking this wizard.

As with any other WinSQL Professional wizard, you have a choice of creating a new template or use an existing template.

The following window displays:

2          If this is the first time you are running this wizard, select Create a new export template. An export template stores all necessary information required to move data from one source to another target and, if saved, can be used for future exports.

OR

Select Use an existing template that you previously created.

3          Click Next to continue. The following window displays:

4          Use the following information to complete the options displayed on this window.

Target table name

If you create scripts for a table, the Target table name field is already populated. However, in the case of a query, you need to specify a table name. This is the name of the table used in the CREATE TABLE and INSERT INTO statements.

Handling New Line characters

Database rows can often contain embedded new line characters. Since a new line (CHAR (13) + CHAR (10)) character has a special meaning in SQL, they must be handled differently when such characters appear in the actual data.

The following table describes each of these options:

 

Option

Description

Replace with

CHAR(13) +

CHAR(10)

This is the default option and will replace all new line characters with an ODBC compliant function call.

Example: Let’s say a field contains the following data.

1 Main Street

Suite 130

This data will be converted to

'1 Main Street' + {fn CHAR(13)} + {fn CHAR(10)} + ‘Suite 130’

Notice the {fn CHAR(13)} statement, which represents an ODBC escape sequence for representing ASCII 13 and 10 characters.

Print as-is

Choosing this option will print the new line characters as-is, meaning the actual INSERT statement may appear in multiple lines.

Ignore

This option will ignore every row that has a new line character.

Comment out

This option will write the rows to the output, but these rows will be commented out. You can later uncomment them and run them manually.

Generate CREATE TABLE statement

When this option is selected, a CREATE TABLE statement is generated before the actual INSERT statements. Note that this CREATE TABLE statement is generated based on the result of a SQL Query. Even if you are exporting every rows from a table, the wizard is going to issue a “select * from tableName” statement and run the wizard. Therefore, the generated CREATE TABLE statement will not have information regarding primary keys, foreign keys, or any index the table may have.

Print query terminator after every record

This option prints a query terminate after the INSERT statement.

5          After selecting all options, click Next, and then click Finish to allow WinSQL Professional to generate the INSERT statements. Once the statements are created, you can either save them to a file or copy them to the Windows Clipboard.