Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: Creating Table With Diff Field Names Every Week.

    Hello All Experts.

    I need to create a new DB table every week but the field names are not fixed. i.e. The field names come from a table which I have captured some info from a text file. This needs to be ran automatically.

    I tried running below script but it doesn't work for me.

    Declare @V1 varchar(15)
    Declare @V2 varchar(15)
    Declare @V3 varchar(15)
    ........

    select @V1=V1, @V2=V2, @V3=V3, ...... from ZPP124_Header

    CREATE TABLE [dbo].[ZPP124] (
    @V1 [varchar] (20) ,
    @v2 [varchar] (20)
    )


    Error
    Server: Msg 170, Level 15, State 1, Line 34
    Line 34: Incorrect syntax near '@V1'.


    If I enclosed @V1 with [ ], the field name will be @V1 instead of the name that was stored in ZPP124_Header file.


    If you have done this before, appreciate if you could advise how I could fix this. Thanks a million.


    Best regards
    Teck Boon

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you considered linked tables?
    This allows you to query the files directly
    Code:
    --Create a linked server to folder (folder must be on the same server as the SQL instance)
    EXEC dbo.sp_AddLinkedServer
         @server     = 'give_it_a_name'
       , @srvproduct = 'Jet 4.0'
       , @provider   = 'Microsoft.Jet.OLEDB.4.0'
       , @locatio    = 'C:\'
       , @provstr    = NULL
       , @catalog    = 'Text'
    GO
    --Login mappings
    EXEC dbo.sp_AddLinkedSrvLogin
         @rmtsrvname = 'give_it_a_name'
       , @useself    = 'True'
    GO
    --List the "tables" in the linked server (this is really a list of file names in the directory)
       EXEC dbo.sp_Tables_Ex 'give_it_a_name'
    GO
    --Query one of the files by using a four-part name. 
    SELECT * 
    FROM   give_it_a_name...sample#txt
    
    --Clean up (drop server and logins)
    EXEC dbo.sp_DropServer
         @server     = 'give_it_a_name'
       , @droplogins = 'DropLogins'
    GO
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •