Data internal code processing in new and old system switching

Original system: MSSQL SERVER 7.0+delphi

New system: MSSQL SERVER + DELPHI

Data to be processed: SPKFK (commodity storehouse):

(SPID,//Product inside Code PK

SPBH,//commodity number Unique Index

SPMCH,//Product name

SHPGG,//Product specifications

DW,//unit

SHPCHD//commodity origin)



The data source in the new system is SPKFK in the original system, and there is no SPID field in the original system, which needs to be generated during the import.

Attaching the old system business Database (DTX4) to the MSSQL2000

Perform the following actions:

Use ST70--New system business Database

DECLARE @tran_point int

Set @tran_point =@ @trancount--Get the number of system transactions

If @tran_point =0 begin tran Tran_c--Start transaction else save Tran Tran_c--Set transaction save point



Declare @spid char (11)--Define variable set @spid = '

DECLARE @id INT--Defining an internal code value variable

Set @id = 0--Initialize starting value

Select @spid as spid, SPBH, SPMCH, SHPGG, DW, shpchd into #t1-extract information from the original system from dtx40. Spkfk

If @ @error <>0 goto Err_lab

Update #t1 set spid= @id, @id = @id +1--Generate code values for merchandise

If @ @error <>0 goto Err_lab

Update #t1 set spid= ' SPH ' + REPLICATE (' 0 ', 8-len (LTrim (spid))) RTrim (+ltrim (SPID))--Generate the inner code of the product (the code for the product is: RTrim) If @ @error <>0 goto Err_lab

Delete from SPKFK--Empty the destination table if @ @error <>0 goto Err_lab

INSERT into SPKFK (spid, SPBH, SPMCH, SHPGG, DW, SHPCHD)-inserts data into the destination table select spid, SPBH, SPMCH, SHPGG, DW, shpchd from # T1

If @ @error <>0 goto Err_lab

drop table #t1

If @tran_point =0 goto RETURNLB

Err_lab:rollback Tran Tran_c--Canceling transactions

Returnlb:commit Tran Tran_c--Submitting a transaction



The above is the code processing of the commodity data, other data ditto.