From the DBASE file, import data into the SQL database, very simple, directly with the following statement:
/ * ================================================================================================================================================================ =================== * / - If the table accepts data imported, there is an Insert INTO table Select * from OpenRowSet ('Microsoft.jet.OleDb.4.0', ' DBASE 5.0; Database = C: / ',' SELECT * from [Test.dbf] ')
- If imported data and generates table Select * INTO table from OpenrowSet ('Microsoft.jet.OleDb.4.0', 'DBASE 5.0; Database = C: /', 'SELECT * from "Test.dbf]')
/ * ================================================================================================================================================================ =================== * / - If you export data from the SQL database, if the dbase file already exists, you can use: Insert Into OpenRowSet 'Microsoft.jet.OleDb.4.0', 'DBASE 5.0; Database = C: /', 'Select * from [Test.dbf]') Select * from table
/ * - Description: Database = C: / c: / is the storage directory of the DBF file 'Select * from [test.dbf] test.dbf refers to the DBF file name - * /
- If the dbase file does not exist, you need to use the following stored procedure.
/ * - Data Export Data Data in DBase Export Table, if the file does not exist, automatically create files based on versatility, only support for export standard data types - * /
/ * - Call example
- Export DBASE P_EXPORTTB @ TBNAME = 'Area Information', @ Path = 'C: /', @ Over = 0 - * / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_exporttb] ') And ObjectProperty (id, n'isprocedure') = 1) Drop Procedure [DBO]. [p_exporttb] Go
CREATE PROC P_EXPORTTB @ tbname sysname, - To export table name @Path nVarchar (1000), - File storage directory @fname nvarchar (250) = '', - file name, default is a table name @Over bit = 0 - Whether to override existing files, if not overwritten, then add asDeclare @err int, @ src nvarchar (255), @ desc nvarchar (255), @ out intdeclare @obj int, @ constr nvarchar (1000), @ SQL VARCHAR (8000), @ fdlist varchar (8000) - Parameter detection if isnull (@fname, ') =' 'set @ fname = @ TBNAME '. dbf '
- Check if the file already exists if right (@ Path, 1) <> '/' set @ path = @ Path '/' crete table #TB (A bit, b bit, c bit) set @ SQL = @ PATH @ FNameInsert Into #TB EXEC MASTER HAWP_FILEEXIST @sqlif exists (select 1 from #tb where a = 1) = @ Over = 1 begin set @ SQL = 'del' @ SQL EXEC MASTER.. pP_CMDSHELL @ SQL, NO_OUTPUT END ELSE Set @ over = 0ELSE set @ over = 1
- Database creation statement set @ SQL = @ path @ fnameset @ constr = 'provike = microsoft.jet.Oledb.4.0; extended proties = "DBASE 5.0;' '; HDR = NO; Database =' @ Path '" '
- Connect database EXEC @ Err = sp_oacreate 'adodb.connection', @ Obj outif @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @10 @err <> 0 goto lberr
- Create a table Sqlselect @SQL = ', @ fdlist =' 'select @ fdlist = @ fdlist ', ' a.name, @ SQL = @ SQL ', [' a.name '] Case When B.Name in ('char', 'nchar', 'varchar', 'nvarchar') Then 'Text (' CAST (Case When A.Length> 250 Ten 250 else a.length End as varchar) ')' When B.Name in ('Tynyint', 'Int', 'Bigint', 'Tinyint') Then 'Int' When B.Name In ('SmallDateTime') Then 'DateTime' When B.Name in ( 'money', 'smallmoney') then 'money' else b.name endFROM syscolumns a left join systypes b on a.xtype = b.xusertypewhere b.name not in ( 'image', 'text', 'uniqueidentifier', ' SQL_VARIANT ',' NTEXT ',' VARBINARY ',' Binary ',' TimeStamp ') And Object_ID (@TBNAME) = IDSELECT @ SQL =' Create Table [' @ Fname '] (' Substring (@ SQL, 2 , 8000) ')', @ fdlist, 2,8000) if @ Over = 1begin Exec @ ire = sp_oamethod @ Obj, 'Execute', @ out out, @ SQL IF @err <> 0 goto lberrend
Exec @ Err = sp_oadestroy @obj
Set @ SQL = 'OpenRowSet (' 'Microsoft.jet.OleDb.4.0', 'DBASE 5.0; Database =' @ Path '', 'SELECT * FROM [' @ fname ']' ')'
- Import Data EXEC ('INSERT INTO' @ SQL '(' @ fdlist ') SELECT' @ fdlist 'from' @ tbname)
Return
LBERR: EXEC SP_OAGETERRORINFO 0, @ src out, @ DESC OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src AS error source, @ DESC AS error Description Select @ SQL, @ Constr, @ fdlistgo
/ * - Data Export DBASE Export Introduction Data into DBASE, if the file does not exist, automatically create files based on versatility, only support the export standard data type - * /
/ * - Call example
- Export dbase p_exporttb @ SQLSTR = 'SELECT * FROM area information', @ Path = 'c: /', @ over = 1 - * /
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_exporttb]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_exporttb] Go
Create Proc P_exportTB @ Sqlstr Varchar (8000), - To Export Query Name @Path Nvarchar (1000), - File Storage Directory @fname NVARCHAR (250) = 'Temp.dbf', - File Name, Default to Temp @over bit = 0 - Whether to override the existing file, if not overwritten, then add asDeclare @err int, @ src nvarchar (255), @ desc nvarchar (255), @ out intdeclare @obj int, @ constr nvarchar (1000), @ SQL VARCHAR (8000), @ fdlist varchar (8000)
- Parameter detection if ISnull (@fname, ') =' 'set @ fname =' Temp.dbf '
- Check if the file already exists if right (@ Path, 1) <> '/' set @ path = @ Path '/' crete table #TB (A bit, b bit, c bit) set @ SQL = @ PATH @ FNameInsert Into #TB EXEC MASTER HAWP_FILEEXIST @sqlif exists (select 1 from #tb where a = 1) = @ Over = 1 begin set @ SQL = 'del' @ SQL EXEC MASTER.. pP_CMDSHELL @ SQL, NO_OUTPUT END ELSE Set @ over = 0ELSE set @ over = 1
- Database creation statement set @ SQL = @ path @ fnameset @ constr = 'provike = microsoft.jet.Oledb.4.0; extended proties = "DBASE 5.0;' '; HDR = NO; Database =' @ Path '" '
- Create a table sqldeclare @tbname = '## tmp _' convert (varchar (38), newid ()) set @ SQL = 'SELECT * INTO [' @ TBNAME '] from (' @ SQLSTR ') a'exec (@SQL) - Connect Database EXEC @ Err = SP_OACREATE' AdoDb.Connection ', @ Obj outif @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @10 @err <> 0 goto lberr
- Create a table Sqlselect @SQL = ', @ fdlist =' 'select @ fdlist = @ fdlist ', ' a.name, @ SQL = @ SQL ', [' a.name '] Case When B.Name in ('char', 'nchar', 'varchar', 'nvarchar') Then 'Text (' CAST (Case When A.Length> 250 Ten 250 else a.length End as varchar) ')' When B.Name in ('Tynyint', 'Int', 'Bigint', 'Tinyint') Then 'Int' When B.Name In ('SmallDateTime') Then 'DateTime' When B.Name in ( 'Money', 'Smallmoney') Then 'Money' Else B.Name Endfrom Tempdb..syscolumns a left a.xtype = B.XUSERTYPEWHERE B.NAME NOT IN ('Image', 'Text' , 'uniqueidentifier', 'sql_variant', 'ntext', 'varbinary', 'binary', 'timestamp') and a.id = (select id from tempdb..sysobjects where name = @ tbname) select @ sql = 'create Table [' @ fname '] (' substring (@ SQL, 2, 8000) ') ', @ fdlist = substring (@ fdlist, 2,8000)
IF @ over = 1begin Exec @ Err = sp_oamethod @ Obj, 'Execute', @ out out, @ SQL IF @err <> 0 goto lberrendexec @ err = sp_oadestroy @obj
Set @ SQL = 'OpenRowSet (' 'Microsoft.jet.OleDb.4.0', 'DBASE 5.0; Database =' @ Path '', 'SELECT * FROM [' @ fname ']' ')'
- Import data EXEC ('INSERT INTO' @ SQL '(' @ fdlist ') select' @ fdlist 'from [' @ TBNAME ']')
Set @ SQL = 'Drop Table [' @ TBNAME ']' EXEC (@SQL)
Return
LBERR: EXEC SP_OAGETERRORINFO 0, @ src out, @ DESC OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src AS error source, @ DESC AS error Description Select @ SQL, @ Constr, @ fdlistgo
