• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:


hi all

i have a AS-400 (which become very slow:) and i need to have 5 tables (in the same LIB) to be replicated every night in a MS SQL server ... Which is the best practice to follow so that every morning the data of these 5 tables will be identical to the as400. Of course i dont want all the tables to be reimported in the SQLSRV but only the new or changed data.

Any info will be much much appreciated

Thanx George
  • 2
2 Solutions
Of course i dont want all the tables to be reimported in the SQLSRV but only the new or changed data.

Unless you have a replication engine driver suitable for SQL Server (you'll better off importing the whole dataset with dup check ignore set on a covering index or pk index and import in this way or use a trigger for the insert.  Any import into SQL Server via a linked server will be slower)


Trigger mechanism:
create trigger Ins_NonDups on tableA for insert

insert table_B (Primary_Key_B, field_B1, field_B2)
/*Excessive bracket removed..*/
select Primary_KeyA, field_A1,field_A2
from inserted
where primary_key_a not in (select Primary_Key_B from table_B)

Index Mechanism Demo:
You would need to to check against the key-columns in the second table as an exclusion criteria to your input query which takes a lot longer than the following demo using ignore dups (my preferred mechanism for this particular type of problem/solution):
The fastest way will be to change your index to ignore dups, as demonstrated in the below example (you don't have to run this in Northwind any test/scratch DB will do-- it only creates one table + one index, and does not take up a lot of space this demo)

/*Check our tables do not exist*/
USE Northwind
      WHERE TABLE_NAME = 'Cust_Payments')
   DROP TABLE Cust_payments*/ /*Uncomment if requring to delete the tables after the demo*/
USE Northwind
/*IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'cust_indpayments')
   DROP INDEX Cust_payments.cust_indpayments */ /*Uncomment if requring to delete the tables after the demo*/

USE Northwind
CREATE TABLE Cust_payments
 cust_ind int NOT NULL, /*=CustomerID in customer table*/
 cust_payment money NOT NULL,
 commissionmade decimal(2, 2) NOT NULL
) /*Create our test table, and some batch data*/
INSERT Cust_payments
   VALUES (1, 310, .1)
INSERT Cust_payments
   VALUES (2, 1500, .5)
INSERT Cust_payments
   VALUES (3, 980, .7)
INSERT Cust_payments
   VALUES (5, 250, .3)
INSERT Cust_payments
   VALUES (8, 75, .6)
GO /*End of population of batch data*/
GO /*Start DEMO*/ /*Add a ignore_dup_key -- you would only need to change your existing key to this type on your destination table*/
   ON Cust_payments(cust_ind)
/*Try insert of ALL dup values -- should not add any*/
insert into Cust_pay
select * from Cust_payments
/*Error returned:
Server: Msg 3604, Level 16, State 1, Line 1
Duplicate key was ignored.*/
/*This does not cause end-of execution of the batch*/

/*Otherwise use the following:*/
insert into Cust_payments
select * from Cust_payments
select 55 as cust_ind, 8036 as  cust_payment, .06 as commissionmade

/*Lets have a look at some of the results now..*/
select * from Cust_payments
/*Notice that 55 has been added all other updates, ignored*/

/*This is the fastest way-sql server rejects duplicates, but procceds to update/add
valid rows*/

The error msg 3604 can be safely ignored from the execution and will not cause the SP to END/fail -- you can ignore this particular error number in certain cases.  Ensure to use SET NO COUNT OFF when using this particular functionality (I've seen some oddities with SET NO COUNT ON and using duplicate indexes for ignoring row updates), You can use @@ROWCOUNT to determine the number of added rows.
Although you don't want to update all the data from the AS400, it may be the easiest route.  We use Microsoft Host Integration Server to allow us to replicate our AS400 data to tables on the SQL Server.  We also use this interface to allow the SQLServer to upload data to the AS400.
If you run the process from the SQL Server box (which you can do to/from the AS400) you would need to implement merge replication (inbuilt part of sql-server, do you have drivers to create a linked server directly from SQL Server -> AS400 (latest odbc/oledb drivers installed on sql-server?)) this way it would only retrieve the latest rows required/update rows as rqd.

(Push subscription from AS400-> SQL Server on demand)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now