stored procedure to insert into a table

• ntk

  Old Hand

  Points: 356

  More actions

  March 20, 2007 at 3:34 pm

  #172834

  Hi,

  Would this sql be correct, I want to write a sp to insert into a table using tables from a remote server, how would I change it if I could'nt query the datasource directly:

  EXEC sp_addlinkedserver 'Server',

     N'SQL Server',

      

  EXEC

  sp_addlinkedsrvlogin 'Server', 'false', '', 'user', 'password'

     

  GO

  IF EXISTS (SELECT name FROM sysobjects WHERE name = N'NEW_PROC'

          AND type = 'P')

      DROP PROCEDURE dbo.NEW_PROC

  GO

  CREATE PROCEDURE NEW_PROC

  AS

  BEGIN

  INSERT INTO TEMP_RESULTS(ID,COLUMNA,COLUMNB)

  SELECT A.ID, A.COLUMNA, A.COLUMNB

  FROM Server.Database.dbo.RESULT A

  INNER JOIN

  Server.Database.dbo.SAMPLE B ON

  A.ID = B.ID

  END

  GRANT ALL ON dbo.NEW_PROC TO PUBLIC

• Camilo Torres

  SSCrazy

  Points: 2034

  More actions

  March 21, 2007 at 5:41 am

  #696114

  Well, the question is Why you cannot query the datasource if you have the a Linked Server.

  You can always do this:

  select *

  into NEW_TABLE

  from

  openquery(LinkedServer,'select * from linked_server_table').