Migrating Microsoft Access Queries
into Transact-SQL Scripts

Most Access queries should be translated into stored procedures and views. Nevertheless, some statements run infrequently by an application developer can be stored as a Transact-SQL script, a text file that ends in the file extension .sql. These files can be run from within SQL Server Query Analyzer.

If you plan to transfer some of your Access queries into .sql files, consider separating the Transact-SQL statements into several scripts, depending on how they are used. For example, you can group together into a script those Transact-SQL statements that must be run with the same frequency. Another script might contain all Transact-SQL statements that are run only under certain conditions. Additionally, Transact-SQL statements that must be run in a specific order should be grouped together in a discrete script.

To move a statement from Access to a Transact-SQL file:

1. Copy the statement into SQL Server Query Analyzer.
2. Use the blue check mark icon to parse the statement.
3. Execute the statement if appropriate.

Developers with MAKE TABLE Access queries have several options in SQL Server. Developers can create either of these:

• A view.

  A view creates the effect of having a dynamic, virtual temporary table that provides the latest information. This is I/O intensive, because it requires the rejoining of the data tables each time a query is issued.

• A temporary table.

  A temporary table creates a snapshot of data for a connected user’s session. You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with double number sign (##table_name). Queries run quickly against temporary tables because they generally use only one table rather than dynamically joining together several tables to obtain a result set.

For more information about temporary tables, see SQL Server Books Online.

Data Transformation Services (DTS) in SQL Server 7.0 allows you to standardize, automate, and schedule the creation of temporary tables by creating packages.

For example, when you migrate the Access 2.0 Northwind sample database, the crosstab that is created for reporting quarterly data becomes either a view or a data transformation that creates a temporary table on a regular basis. For more information about DTS, see SQL Server Books Online.