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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
fetobaAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.