INSERT into Access From SQL

  • I have a question regarding data inserts to and from MS Access and SQL Server.

    Basically I have been able to INSERT an Access DB Table INTO SQL using:

    SELECT * INTO [20040817].dbo.Employee FROM  OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\Database\00000002.mdb";User ID=Admin;Password=; ')...[Employee] 

    But now I need to go the other way...I need to do an INSERT Into MS Access from SQL Server.  It is important that it works without knowing the Column Names...(It must be dynamic in nature).

    How do I twist the above query to work in the reverse order?  I tried:

    INSERT INTO OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',

    'Data Source="D:\Database\00000001.mdb";User ID=Admin;Password=; ')...Employee

    VALUES (SELECT * FROM [20040817].dbo.Employee)

     

    But none of that works.

    Please help.

     

     

     

  • ron - here's a link that should give you pointers in the right direction...

    link







    **ASCII stupid question, get a stupid ANSI !!!**

  • I think that gets me closer but now when I use this command:

    INSERT INTO OPENROWSET(

    'Microsoft.Jet.OLEDB.4.0','Data Source="D:\Database\00000001.mdb";

    User ID=Admin;Password=','SELECT * FROM EMPLOYEE' )

    SELECT * FROM [20040817].dbo.Employee

    I get the following error:

    Server: Msg 7399, Level 16, State 1, Line 36

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. 

    [OLE/DB provider returned message: Could not find installable ISAM.]

    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].

     

  • I needed something similar for an archive routine and wrote this sproc, which is completely generic and dynamic in nature. I've tested it on a table with over 5 million rows, DTS takes around 2 hours and this sproc runs in about 7 minutes on same server! I've stripped out the error handling to make it easier to read.

    The procedure builds the column names dynamically and uses dynamic SQL to execute the OpenRowSet based Insert query. Assumes source and target tables are identical.

    You may need to increase the MaxLocksPerFile entry in the registry, I couldn't figure out how to get the query to take out a table lock on the access table, changing the open options in the Access database didn't seem to make any difference.

    -------------------------------------------------------------------------------

    Exec dcp_Export_Table 

     ,@sSrcTableName  = 'tbl_dnd_conveyor'

     ,@sDestTableName = 'tbl_dnd_conveyor'

     ,@sPathName  ='E:\DataCentral\Provisioning\Temp\Conveyor.mdb'

    -------------------------------------------------------------------------------

    IF EXISTS (SELECT name FROM   sysobjects WHERE  name = N'dcp_Export_Table' AND type = 'P')

        DROP PROCEDURE dcp_Export_Table

    GO

    CREATE PROCEDURE dcp_Export_Table

     ,@sSrcTableName  Varchar(100)

     ,@sDestTableName Varchar(100)

     ,@sPathName  Varchar(255)

    AS

    /*

    Module      : dcp_Export_Table

    Description : Export named table to named Access Mdb file

    Modification History

    ====================

    Date  Author  Description

    --------------- --------------- ----------------------------------------------------------------------------

    06-09-2005 Danish Janjua Created

    */

    BEGIN

    SET NOCOUNT ON

    --

    -- *** Logging & Error Check variables *** --

    --

    Declare

      @sDbName  sysname  -- Holds the Name of the Database where this procedure is being run

     ,@sProcedureName sysname  -- Procedure name, used for Logging

     ,@sStepName  Varchar(50) -- Step name within procedure, used for logging

     ,@sMessage  Varchar(255) -- Used for Procedure Logging

     ,@iError  Int  -- Error value

     ,@iRetVal  Int  -- Return values returned from SP or XP

     ,@sFirstTimeThru Char(1)

     ,@sColumnName  Varchar(50)

     ,@sTableColumnList Varchar(2000)

     ,@sSqlCommand  nVarchar(2000)

    --

    -- *** Initialise Process logging variables *** --

    --

     Select @sDbName  = DB_NAME()

     Select @sProcedureName = OBJECT_NAME(@@Procid)

     Select @iError  = 0

     Select @iRetVal  = 0

     Set NoCount On

    -----------------------------------------------------------------------------------------------------------------------

    -- STEPA: PROCEDURE BEGIN

    -----------------------------------------------------------------------------------------------------------------------

    -----------------------------------------------------------------------------------------------------------------------

    -- STEPB: Create a comma delimited list of column names into a string

    -----------------------------------------------------------------------------------------------------------------------

    Select @sTableColumnList = ''

    Select @sFirstTimeThru = 'Y'

    Declare dcp_columns_cursor Cursor For

     Select  Column_Name

     From  Information_Schema.Columns

     Where  table_catalog  = @sDbName

     and  table_schema  = 'dbo'

     and table_name  = @sSrcTableName

    Open dcp_columns_cursor

    Fetch Next From dcp_columns_cursor  Into   @sColumnName

    While @@FETCH_STATUS = 0

    Begin

     If @sFirstTimeThru = 'Y'

      Begin

       Select @sTableColumnList = @sColumnName

       Select @sFirstTimeThru = 'N'

      End

     Else   

      Select @sTableColumnList = @sTableColumnList + ',' + @sColumnName

     Fetch Next From dcp_columns_cursor  Into   @sColumnName

    End

    Close dcp_columns_cursor

    Deallocate dcp_columns_cursor

    -----------------------------------------------------------------------------------------------------------------------

    -- STEPC: Export table to Mdb file

    -----------------------------------------------------------------------------------------------------------------------

    Select @sSqlCommand = 'INSERT INTO OPENROWSET '

          + '(''Microsoft.Jet.OLEDB.4.0'','      -- provider

          + '''' + @sPathName + ''' ; ''Admin'' ; '''' '    -- path & access details

          + ', ' + @sDestTableName + ')'      -- destination table

          + ' (' + @sTableColumnList + ') '      -- column names

          + 'Select ' + @sTableColumnList + ' From ' +  @sSrcTableName + ' (NoLock)' -- source query

    --

    -- Execute it!

    --

    Execute sp_executesql @sSqlCommand

    -----------------------------------------------------------------------------------------------------------------------

    -- STEPZ: PROCEDURE END

    -----------------------------------------------------------------------------------------------------------------------

    NORMALEXIT:

    Set NoCount Off

    Return

    END

     

  • That is awesome stuff!

     

    Thanks a ton!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply