January 27, 2004 at 8:15 am
We're using SQL Server 2000, SP3. I'm creating a store procedure on a server that INSERTS into another database on a different server via Linked servers. Initially it creates, inserts and updates to a temporary table before it inserts to the permanent table on the other server. Now I've been told that they don't want me to use a direct INSERT statement across servers, but want me to create a stored procedure on the other server that does the INSERT. I can call the stored procedure from the first server and INSERT using a cursor. However, is there a better way to do the INSERT without using a cursor, but still calling a stored procedure on the second server to do the INSERT?
January 27, 2004 at 8:52 am
It seems you tried to insert more than one records from server1 table to server2 table.
Create linked server server1 to be linked to server2, create procedure to perform insert like insert into table2 select * from server1.dbname.dbo.table1 in server2 and call this sp from server1.
January 27, 2004 at 9:06 am
Sorry, didn't think to mention that the temporary table on server1 contains over 50 fields, and can have around 2,000 rows. Yes, the 2 servers are already linked.
Does your soultion work, if the sproc on server1 uses temparary tables?
January 27, 2004 at 1:09 pm
I doubt about it but why you can't use a permanent table?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply