asked on

How to move records from one SQL server table to another SQL server table without possible missing?

Hi,

I have hit a problem on moving SQL table from one to another.

Currently, my company policy do not allow us to expose any production DB on DM zone and let others to insert data into our database. Yet, due to the need of receiving trade details from brokerage firm after our trader making some trades, we need the brokerage firm to insert data via SQL DB insert.

To solve this, we build a dump SQL DB (in fact, MS SQL express) on a machine which place on the DM zone and only allow the brokerage firm to insert trade details into that DB and apart from that, no other authorities. And within our production server farm, we have created a schedule job to check the DB in DM zone and see whether any incoming records on a 2-minute basis. In case new records found, I will copy them from the DM zone DB table back to the production server farm DB table and then delete all the copied records from the DM zone DB table.

All seems fine, but recently, trader found missing trade records and I doubt it is because during the course copying those trade records from DM zone DB, new records coming in and after copying, I delete them all together with the new incoming record.

For example, there are 3 records in the DM zone DB being detected. And I copy all those 3 records to the production server farm DB. but during this very short period of time, 1 new trade record come in. And after the copy completed, I delete all those 4 records and make lost trade records happened.

May I know how to script my stored procedure, so as to avoid this to happen again?

Kindly please help urgently.

Stanley

Jonathan Kelly

ser

Jonathan Kelly

add a boolean field to your db in dm zone and set it to default
false.
after copy complete set flag true.

alter your delete statementvto only delete where true.

Jonathan Kelly

or something like the following

create table #TraderMAIN (
pkey_id int identity not null, traderData varchar(100)
)
go

create table #TraderDMZ (pk_id int identity not null, traderData varchar(100))
go

insert into #TraderDMZ (traderData) values ('trader data ' + convert(varchar(20),getdate(),114)) --some dummy data
go 3

declare @DMZ_REC_ID int
While (Select Count(*) From #TraderDMZ) > 0 --while there are DMZ records
	Begin 
		select top 1 @DMZ_REC_ID = pk_id from #TraderDMZ --for each record
		insert into #TraderMAIN (traderData) select traderData from #TraderDMZ  where pk_id = @DMZ_REC_ID --copy record
		delete #TraderDMZ where pk_id = @DMZ_REC_ID --delete from  record DMZ
	End

select * from #TraderDMZ
select * from #TraderMain 

drop table #tradermain
drop table #TraderDMZ
go

Open in new window

Stanley Lai

ASKER

Hi Jonathan,

Seems your suggestion on adding boolean field to the DB in DM zone suits my case more as the primary key of trade detail table is composed of several fields rather than just a serial number. And if I need to add a serial number as the primary field, I would prefer to add one more boolean field at the end rather than altering the key of the file.

Yet, due to my limited knowledge on SQL server, I don't know how to add that boolean field at the end without affecting the operation of the brokerage firm's DB insertion.

For example, the trade detail file contains 10 fields originally with the first 3 fields make up of the primary key. And the brokerage firm will insert those 10 fields to our DM zone DB on every trade we made. If I added 1 boolean field at the end as the 11th field, next time when our counterparty insert a new record, will it be turn out into error? Since they only provide 10 fields of data but the table contains 11 fields now ... may I know how to let them still providing us 10 fields and can insert into our 11 field table with the last boolean field default to be "false" automatically?

Kindly please help

Stanley

Jonathan Kelly

If you want to run with the boolean field solution than you can set it to default to false or 0.

Alternatively, you could alter my example and use the each field of the pk ..

declare @DMZ_REC_ID int
declare @DMZ_REC_ID_Field2 int
declare @DMZ_REC_ID int_Field3 int

While (Select Count(*) From #TraderDMZ) > 0 --while there are DMZ records
	Begin 
		select top 1 @DMZ_REC_ID = pk_id, 
                                        DMZ_REC_ID_Field2 = pk_field2,
                                        DMZ_REC_ID_Field3 = pk_filed3
                     from #TraderDMZ --for each record

		insert into #TraderMAIN (traderData) select traderData from #TraderDMZ  where 
                pk_id = @DMZ_REC_ID AND 
                pk_field2= @DMZ_REC_ID  AND
                pk_field3= @DMZ_REC_ID 

		delete #TraderDMZ where 
                pk_id = @DMZ_REC_ID AND
                pk_field2= @DMZ_REC_ID_fIELD2 AND
                pk_field3= @DMZ_REC_ID_FIELD3 --delete from  record DMZ
	End

Open in new window

If the traders are sending you a primary key, is that primary key the same as the primary key you use in your MAIN table OR is that \ or can that field combination be used to uniquely identity that record, in your main table?

If it is you can simply insert from the DMZ table straight to your MAIN table with one insert statement. The Primary(or unique) Key will stop you adding the same DMZ record a second time - thereby you do not need to ever delete the DMZ records - simplifying the solution and removing the While Loop.

ASKER CERTIFIED SOLUTION

Jonathan Kelly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stanley Lai

ASKER

Hi Jonathan,

Seems your last script fits my purpose, but I need to amend it a bit and test it out. Please give me some time to verify. In case it performs as expected, I would get back to you and close the case as soon as possible. Thanks ^^

Cheers
Stanley

Jonathan Kelly

ok great

Jonathan Kelly

thanks for the points.
hope it works well for you.

Stanley Lai

ASKER

The solution now work for me. So far so good ^^. Thanks