August 19, 2005 at 4:18 pm
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.
August 22, 2005 at 7:45 am
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: ].
September 7, 2005 at 3:07 am
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
September 7, 2005 at 7:49 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy