How to use SQL pass-through to SELECT INTO a new SQL Server table in Visual FoxPro (238904)

MORE INFORMATION

In order to issue a SELECT-SQL statement that creates a new SQL Server table, the select into/bulkcopy option must be enabled for the SQL Server database. By default, the select into/bulkcopy option is disabled in newly-created SQL Server databases. The select into/bulkcopy option can be modified with the sp_dboption stored procedure. The syntax for changing the status of the select into/bulkcopy option is:

sp_dboption 'pubs','select into/bulkcopy','true'

-or-

sp_dboption 'pubs','select into/bulkcopy','false'

sp_dboption changes settings for a SQL Server database.

Note Executing permissions to change an option in SQL Server, using the sp_dboption stored procedure, defaults to members of the sysadmin fixed server role or the db_owner fixed database role for the database for which the option is to be changed.

The following code snippet uses SQL Pass Through to select all records from the Authors table of the Pubs database into a new table called Mytable in the Pubs database. This assumes that the user has the appropriate permissions and that the correct values are supplied.

*!* Note Please change the SERVER= segment of the following line to reflect
*!* the name of the SQL Server you will be connecting with .
gnConnHandle=SQLSTRINGCONN('DRIVER={SQL Server};SERVER=MY_SERVER;DATABASE=MASTER;' + ;
   'UID=UserName;PWD=StrongPassword')
IF gnConnHandle>0
   *!* Query to determine whether SELECT INTO and Fast Bulk Copy are allowed
   sqlcommand="sp_dboption 'pubs','select into/bulkcopy'"
   =sqlexec(gnConnHandle,sqlcommand,'bulkcopy')
   sqlcommand="IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE " + ;
      "table_name = 'mytable') " +CHR(13) + CHR(10) + "DROP TABLE mytable"
   =sqlexec(gnConnHandle,sqlcommand)
   IF UPPER(ALLTRIM(BULKCOPY.CURRENTSETTING))="OFF"
      *!* SELECT INTO and Fast Bulk Copy not allowed by default.
      *!* Enable SELECT INTO and Fast Bulk Copy in the selected database
      sqlcommand="EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'"
      =sqlexec(gnConnHandle,sqlcommand)
   ENDIF
   *!* Use the PUBS database
   sqlcommand="USE pubs"
   =sqlexec(gnConnHandle,sqlcommand)
   *!* Issue a SQL Select command and SELECT INTO a new table on SQL Server
   sqlcommand="SELECT * INTO mytable" + CHR(13)+CHR(10) + ;
      "FROM authors"
   =sqlexec(gnConnHandle,sqlcommand)
   *!* Use the MASTER database
   sqlcommand="USE master"
   =sqlexec(gnConnHandle,sqlcommand)
   IF UPPER(ALLTRIM(BULKCOPY.CURRENTSETTING))="OFF"
      *!* If SELECT INTO and Fast Bulk Copy were originally disabled,
      *!* Reset SELECT INTO and Fast Bulk Copy in the selected database to original value
      sqlcommand="EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'"
      =sqlexec(gnConnHandle,sqlcommand)
   ENDIF
   *!* Select from the table that was just created.
   sqlcommand="select * from pubs..mytable"
   =sqlexec(gnConnHandle,sqlcommand,'mycursor')
   =sqldisconn(gnConnHandle)
   SELECT mycursor
   BROWSE NOWAIT
ENDIF