用openrowset吧 insert inot 表 select * from openrowset('msdasql','driver=?','select * from 表')
database desktop 只是一个工具吧。晕。
/*************导出到Access********************/ insert into openrowset('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表/*************导入Access********************/ insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'',A表)**************导入DBF文件****************/ select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=e:\VFP98\data; SourceType=DBF', 'select * from customer where country != "USA" order by country') go /***************** 导出到DBF ***************/ 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'select * from [aa.DBF]') select * from 表说明: SourceDB=c:\ 指定foxpro表所在的文件夹 aa.DBF 指定foxpro表的文件名. ** 导入文本文件 EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件 EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword' 或 EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本,用逗号分开 exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
还是不行啊!… 数据格式是 pradox 7 的,怎么导入进去啊!
用DTS导入,存储包到sql,然后做个sql的调度
set @exec='select * into '+@table_name+' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',,''Paradox 7.x;HDR=NO;IMEX=2DATABASE='+@path+''',''select * from '+@table_name+''')'exec (@exec)
set @exec='select * into '+@table_name+' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Paradox 7.x;HDR=NO;IMEX=2DATABASE='+@path+''',''select * from '+@table_name+''')'exec (@exec)
set @exec='select * into '+@table_name+' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',,''Paradox 7.x;HDR=NO;IMEX=2;DATABASE='+@path+''',''select * from '+@table_name+''')'exec (@exec)
select * into dd_DD from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Paradox 7.x;HDR=NO;IMEX=2;DATABASE=D:\寶蘭版舊帳\OLDData2002','select * from CONTIN.DB') 执行这个,提示如下: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' reported an error. [OLE/DB provider returned message: 无效的操作。] OLE DB error trace [OLE/DB Provider 'MICROSOFT.JET.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
哦 select * into dd_DD from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Paradox 7.x;HDR=NO;IMEX=2;DATABASE=D:\寶蘭版舊帳\OLDData2002','select * from CONTIN') 不用加.db
select * into dd_DD from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Paradox 7.x;HDR=NO;IMEX=2;DATABASE=D:\OLDData2002','select * from CONTIN') 在QUERY 里执行,还是报错如下: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' reported an error. [OLE/DB provider returned message: 无效的操作。]
insert inot 表 select * from openrowset('msdasql','driver=?','select * from 表')
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表说明:
SourceDB=c:\ 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.
** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本,用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Paradox 7.x;HDR=NO;IMEX=2;DATABASE=D:\寶蘭版舊帳\OLDData2002','select * from CONTIN.DB')
执行这个,提示如下:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 无效的操作。]
OLE DB error trace [OLE/DB Provider 'MICROSOFT.JET.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
select * into dd_DD from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Paradox 7.x;HDR=NO;IMEX=2;DATABASE=D:\寶蘭版舊帳\OLDData2002','select * from CONTIN')
不用加.db
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Paradox 7.x;HDR=NO;IMEX=2;DATABASE=D:\OLDData2002','select * from CONTIN')
在QUERY 里执行,还是报错如下:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 无效的操作。]