Linked Server multiple record insert using Execute AT

I want to insert multiple records from my local SQL Server to an remote AS400 server through a Linked Server. I already have this accomplished with OPENQUERY and/or Four Part Name syntex. But it is too slow so I want to use EXECUTE AT syntex to move the load to the Remote AS400 server.

Now I know the EXECUTE AT syntex will go something like:

EXECUTE ('INSERT INTO [REMOTESERVER] (c1,c2...) SELECT c1,c2... FROM [LOCALSERVER] ') AT [LINKEDSERVER]

now, how do i qualify the [LOCALSERVER] so that the as400 remote server can get at the data on my localserver, bottom line how do i pass the local records in this way. I know how to do it for a single record using the VALUES syntex.

thx
fetobaAsked:
Who is Participating?
 
fetobaConnect With a Mentor Author Commented:
INSERT INTO OPENQUERY([Linked Server], 'SELECT c1,c2,..cN FROM [3 Part TableName]')
SELECT TOP 300 c1,c2,..cN
FROM [LocalTable] WITH (NOLOCK)
WHERE [stuff]

BTW, I decided to process/insert the messages using the EXECUE AT [Linked Server] with parallel threads and i do one at a time. Single thread can do 10 records/sec.
0
 
gothamiteCommented:
I don't think you can do that unless the AS400 as some way of referencing your localserver. If these were both SQL servers you would need to have a linked server set up on the remote server pointing back to your server. That makes what you are trying to do equivalent to your original (slow) query.

Is this some for some sort of bulk load/ETL task? If so I would consider using SSIS instead.
0
 
fetobaAuthor Commented:
Oh ok thx..

It's actually real time integration that has a flow of about 10 records per second and sometimes backs up on records, right now I insert batches of top 300 records using OPENQUERY and it varies how long it takes sometimes 21 sec other times over a min..

Any thoughts and help for an alterative are appreciated
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gothamiteCommented:
You could maybe set up a Service Broker queue to process the transactions asynchronously. This would mean that your source wouldn't be held up by waiting for the AS400. Or you could make a SSIS package that just runs in a loop looking for new records to transfer and sending them.
0
 
fetobaAuthor Commented:
I will try the SSIS package route it is more suited for what i am doing, i will post back my results

thx again
0
 
LowfatspreadCommented:
can you post the openquery you use
0
All Courses

From novice to tech pro — start learning today.