USE pubs GO SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS a GOD. Use OPENROWSET and another table in an INNER JOIN This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.Note This example assumes that Access is installed. USE pubs GO SELECT c.*, o.* FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS o ON c.CustomerID = o.CustomerID
/* 功能:还原数据库,从指定ACCESS数据库中还原至SQL数据 说明:注意表是存在标识的处理 创建:zh 日期:2006-10-12 调试:exec Cfg_DeoxidizeData 'MJCfgDepartment','c:\\3.mdb' */ --select * from MJCfgDepartment if exists(select * from sysobjects where name='Cfg_DeoxidizeData') drop proc Cfg_DeoxidizeData go create procedure Cfg_DeoxidizeData @TableName varchar(50), @AccessFilePath varchar(1000) AS declare @txtsql varchar(4000), @FieldStr nvarchar(2000), @FieldStrIn nvarchar(2000), @IsExists nvarchar(2)
set @txtsql='' set @FieldStr='' set @FieldStrIn='' select @IsExists=OBJECTPROPERTY(OBJECT_ID(@TableName),'TableHasIdentity') --判断是否存在标识列
--select @FieldStr=@FieldStr + ',' + name from dbo.syscolumns where id=object_id(@TableName) --求出表中所有字段的连接字符串 --select @FieldStr=stuff(@FieldStr,1,1,'') --用‘’替换第一个字符
select @FieldStr=@FieldStr + ',' + name, @FieldStrIn = case when Length>255 then @FieldStrIn + ',cast(' + name +' as nvarchar(' + cast(Length as varchar(100)) +'))' else @FieldStrIn + ',' + name end from dbo.syscolumns where id=object_id(@TableName) --求出表中所有字段的连接字符串 select @FieldStr=stuff(@FieldStr,1,1,'') --用‘’替换第一个字符 select @FieldStrIn=stuff(@FieldStrIn,1,1,'') --用‘’替换第一个字符
if (@TableName = 'MJCfgDepartment') begin drop table MJCfgDepartment end if (@IsExists = 1) --存在标识列 begin
set @txtsql = 'delete from '+ @TableName --删除表中数据 set @txtsql = @txtsql + ' set IDENTITY_INSERT '+ @TableName +' on' --设置ON set @txtsql = @txtsql + ' insert into '+ @TableName + '(' + @FieldStr + ')select ' --插入数据 set @txtsql = @txtsql + @FieldStrIn +' From OpenDataSource(' set @txtsql = @txtsql + '''Microsoft.Jet.OLEDB.4.0''' set @txtsql = @txtsql + ',' set @txtsql = @txtsql + '''Data Source=' set @txtsql = @txtsql + @AccessFilePath set @txtsql = @txtsql + ';Persist Security Info=False''' set @txtsql = @txtsql + ')...' set @txtsql = @txtsql + @TableName
set @txtsql = @txtsql + ' set IDENTITY_INSERT '+ @TableName +' off' --设置Off print @txtsql Execute (@txtsql) end else begin
set @txtsql = 'delete from '+ @TableName --删除表中数据 set @txtsql = @txtsql + ' insert into '+ @TableName + '(' + @FieldStr + ')select ' set @txtsql = @txtsql + @FieldStrIn +' From OpenDataSource(' set @txtsql = @txtsql + '''Microsoft.Jet.OLEDB.4.0''' set @txtsql = @txtsql + ',' set @txtsql = @txtsql + '''Data Source=' set @txtsql = @txtsql + @AccessFilePath set @txtsql = @txtsql + ';Persist Security Info=False''' set @txtsql = @txtsql + ')...' set @txtsql = @txtsql + @TableName print @txtsql exec(@txtsql) end
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
GOD. Use OPENROWSET and another table in an INNER JOIN
This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.Note This example assumes that Access is installed.
USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
如果在SQL Server中处理,可以用SQL实现。
Access中的两个源表之间是什么关系?
Access中只有一个表,二个表的是在SQL中.因为要返回一个ID提供给另一个表,所以觉得有点难度,如果是单一的一个Access表到SQL的一个表的话,是很容易的.
功能:还原数据库,从指定ACCESS数据库中还原至SQL数据
说明:注意表是存在标识的处理
创建:zh
日期:2006-10-12
调试:exec Cfg_DeoxidizeData 'MJCfgDepartment','c:\\3.mdb'
*/
--select * from MJCfgDepartment
if exists(select * from sysobjects where name='Cfg_DeoxidizeData')
drop proc Cfg_DeoxidizeData
go
create procedure Cfg_DeoxidizeData
@TableName varchar(50),
@AccessFilePath varchar(1000)
AS
declare @txtsql varchar(4000),
@FieldStr nvarchar(2000),
@FieldStrIn nvarchar(2000),
@IsExists nvarchar(2)
set @txtsql=''
set @FieldStr=''
set @FieldStrIn='' select @IsExists=OBJECTPROPERTY(OBJECT_ID(@TableName),'TableHasIdentity') --判断是否存在标识列
--select @FieldStr=@FieldStr + ',' + name from dbo.syscolumns where id=object_id(@TableName) --求出表中所有字段的连接字符串
--select @FieldStr=stuff(@FieldStr,1,1,'') --用‘’替换第一个字符
select @FieldStr=@FieldStr + ',' + name,
@FieldStrIn =
case when Length>255
then @FieldStrIn + ',cast(' + name +' as nvarchar(' + cast(Length as varchar(100)) +'))'
else
@FieldStrIn + ',' + name
end
from dbo.syscolumns where id=object_id(@TableName) --求出表中所有字段的连接字符串 select @FieldStr=stuff(@FieldStr,1,1,'') --用‘’替换第一个字符
select @FieldStrIn=stuff(@FieldStrIn,1,1,'') --用‘’替换第一个字符
if (@TableName = 'MJCfgDepartment')
begin
drop table MJCfgDepartment
end if (@IsExists = 1) --存在标识列
begin
set @txtsql = 'delete from '+ @TableName --删除表中数据 set @txtsql = @txtsql + ' set IDENTITY_INSERT '+ @TableName +' on' --设置ON set @txtsql = @txtsql + ' insert into '+ @TableName + '(' + @FieldStr + ')select ' --插入数据
set @txtsql = @txtsql + @FieldStrIn +' From OpenDataSource('
set @txtsql = @txtsql + '''Microsoft.Jet.OLEDB.4.0'''
set @txtsql = @txtsql + ','
set @txtsql = @txtsql + '''Data Source='
set @txtsql = @txtsql + @AccessFilePath
set @txtsql = @txtsql + ';Persist Security Info=False'''
set @txtsql = @txtsql + ')...'
set @txtsql = @txtsql + @TableName
set @txtsql = @txtsql + ' set IDENTITY_INSERT '+ @TableName +' off' --设置Off
print @txtsql
Execute (@txtsql)
end
else
begin
set @txtsql = 'delete from '+ @TableName --删除表中数据
set @txtsql = @txtsql + ' insert into '+ @TableName + '(' + @FieldStr + ')select '
set @txtsql = @txtsql + @FieldStrIn +' From OpenDataSource('
set @txtsql = @txtsql + '''Microsoft.Jet.OLEDB.4.0'''
set @txtsql = @txtsql + ','
set @txtsql = @txtsql + '''Data Source='
set @txtsql = @txtsql + @AccessFilePath
set @txtsql = @txtsql + ';Persist Security Info=False'''
set @txtsql = @txtsql + ')...'
set @txtsql = @txtsql + @TableName
print @txtsql
exec(@txtsql)
end
GO