Querying a text file via linked server passing file name to procedure

• camlern

  SSC-Addicted

  Points: 425

  More actions

  June 25, 2004 at 12:22 pm

  #108712

  I have a stored procedure to query a text file in a linked server.  This works fine when the file name in hard coded into the procedure.  As this filename changes I need to be able to pass the filename to the procedure.  I can't seem to get the syntax correct for the statement to recognize the filename variable as a table name in the linked server.  This statement works fine:

  select TextFile.*

  INTO #TempTextFile

  FROM TextFileServer...[TA_200#TXT] as TextFile

   left outer join Imports_TA

   on TextFile.SSN=Imports_TA.SSN

    and TextFile.Date_Assess = Imports_TA.Date_Assess

    and TextFile.Transmit_Date = Imports_TA.Transmit_Date

  WHERE Imports_TA.SSN is null

  I need to be able to pass the table name as a variable to replace the 'TA_200#TXT'.  I am also selecting only records in this file that have not been previously imported as the text file is cumulative.  I already have a process to update the schema.ini with the new file name so I just need to be able to run the statement to connect to the file.

  Can anyone offer help with this?

  Thanks,

  Cammy

• noeld

  SSC Guru

  Points: 96590

  More actions

  June 25, 2004 at 6:27 pm

  #512066

  Declare @stm varchar(4000), @tableName varchar(255)

  Set @tableName = 'TA_200.TXT'

  Set @stm = 'select TextFile.* ' +

  'INTO REGULAR_TABLE ' +

  'FROM TextFileServer...['+@tableName+'] as TextFile ' +

  ' left outer join Imports_TA ' +

  ' on TextFile.SSN=Imports_TA.SSN' +

  '  and TextFile.Date_Assess = Imports_TA.Date_Assess ' +

  '  and TextFile.Transmit_Date = Imports_TA.Transmit_Date ' +

  'WHERE Imports_TA.SSN is null'

  exec (@stm)

  

  HTH

   

  
  * Noel