Microsoft Access How To Insert Data Into Table Using Vba Ashani Ashani. Microsoft Access Tutorial for Beginners. SQL Adding Only New Rows With INSERT INTO (Programming In Access 2013. Microsoft Access How To Insert Data Into Table Using Vba Ashani Ashani. Microsoft Access Tutorial for Beginners - Duration. SQL Adding Only New Rows With INSERT INTO.
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:DataCentralProvisioningTempConveyor.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)
Access Vba Insert Into Select
,@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 *** --
--
Microsoft Access 2010 Vba Insert Into Table Sql Server
Select @sDbName = DB_NAME()
Select @sProcedureName = OBJECT_NAME(@@Procid) How to install vipkid app on mac.
Select @iError = 0
Select @iRetVal = 0
Set NoCount On
-----------------------------------------------------------------------------------------------------------------------
-- STEPA: PROCEDURE BEGIN
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
-- STEPB: Create a comma delimited list of column names into a string
----------------------------------------------------------------------------------------------------------------------- https://cosmicpowerup319.weebly.com/john-deere-650-shop-manual-download.html.
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' Best apps for presentations on mac.
and table_name = @sSrcTableName
Open dcp_columns_cursor
Microsoft Access Insert Into Table
Fetch Next From dcp_columns_cursor Into @sColumnName
While @@FETCH_STATUS = 0
Begin
If @sFirstTimeThru = 'Y'
Begin
Select @sTableColumnList = @sColumnName
Select @sFirstTimeThru = 'N'
End
Ms Access Insert Into Sql
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
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |