Code implementation Database conversion [SQL2000 VS DBF format]

Data | database | Conversion here, a summary of the transformation of the SQLSERVER,DBF two forms is made.

One: From the dBASE file, import data into the SQL database, very simple, directly using the following statement:

/*===================================================================*/
--If the table that accepts the data import already exists
INSERT INTO table SELECT * FROM
OpenRowset (' MICROSOFT. JET. oledb.4.0 '
, ' DBase 5.0;database=c:\ ', ' select * from [TEST.DBF] '

--If you import data and generate a table
SELECT * Into table from
OpenRowset (' MICROSOFT. JET. oledb.4.0 '
, ' DBase 5.0;database=c:\ ', ' select * from [TEST.DBF] '

/*===================================================================*/
-If you export data to dBASE from a SQL database, if the dBASE file already exists, you can simply 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 DBF files
' SELECT * from [TEST.DBF] test.dbf refers to the DBF filename
--*/



If the dBASE file does not exist, you need to use the following stored procedure.



/*--Data export dbase

Export the data from the table to dBASE, and if the file does not exist, the file will be created automatically
Only export standard data types are supported based on commonality considerations
--*/

/*--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--Name of the table to be exported
@path nvarchar (1000),--File storage directory
@fname nvarchar (250) = ',--filename, default to table name
@over bit=0-Overwrite files that already exist, append directly if not overridden
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @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 + ' \ '
CREATE TABLE #tb (a bit,b bit,c bit)
Set @sql = @path + @fname
INSERT into #tb exec master. Xp_fileexist @sql
if exists (select 1 from #tb where a=1)
If @over =1
Begin
Set @sql = ' del ' + @sql
EXEC master.. xp_cmdshell @sql, No_output
End
Else
Set @over =0
Else
Set @over =1

--Database Creation statement
Set @sql = @path + @fname
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "DBASE 5.0; '
+'; Hdr=no;database= ' + @path + ' "'

--Connecting to the database
exec @err =sp_oacreate ' adodb.connection ', @obj out
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @obj, ' open ', NULL, @constr
If @err <>0 goto Lberr

--Create a table SQL
Select @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 then else a.length end as varchar) + ') '
When b.name in (' Tynyint ', ' int ', ' bigint ', ' tinyint ') then ' int '
When b.name in (' smalldatetime ', ' datetime ') Then ' datetime '
When B.name in ("Money", ' smallmoney ') Then ' money '
else B.name End
From syscolumns a LEFT join systypes B on A.xtype=b.xusertype
where B.name not in (' image ', ' text ', ' uniqueidentifier ', ' sql_variant ', ' ntext ', ' varbinary ', ' binary ', ' timestamp ')
and object_id (@tbname) =id
Select @sql = ' CREATE TABLE [' + @fname
+ '] (' +substring (@sql, 2,8000) + ') '
, @fdlist =substring (@fdlist, 2,8000)

If @over =1
Begin
EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
If @err <>0 goto Lberr
End

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 out
Lbexit:
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist

Go