From: esquel@sommarskog.se   
      
   Rick_Ch (rick_cheetham@hotmail.com) writes:   
   > I wish to create a file that holds data, that I can use in Query   
   > analyser to insert data into a database. How do I go about doing this,   
   > or where can I find details of how to do it?   
   > Do I use comma separated values and a new line for each field?   
      
   This can be done in several ways and in different formats. Comma-separated   
   values with a new line for each new record (not each field) is a common   
   format. Such a file can be entered into table with BCP command which   
   you run from a command-line window (not from Query Analyzer):   
      
     BCP yourdb.dbo.yourtable in yourdata.csv -U user -P pwd -c -t, -S Server   
      
   Replace the -U and -P option with -T if you are logging in with integrated   
   security.   
      
   One thing to observe here is that BCP does not understand quoted formats.   
   That is, if a line in a file for a table with four columns look like this:   
      
      1,1967,"Dave, Dee, Dozy, Mich & Tich",Zabadak   
      
   BCP will identify the values   
      
     1   
     1967   
     "Dave   
      Dee, Dozy, Mich & Tich",Zabadak   
      
   It is possible to deal with thís by using a format file that specifies the   
   delimiters field by field. However, this will not work if quotes are only   
   applied when needed.   
      
   Rather than using comma as delimiter, you can use tab or any character   
   sequence you fancy.   
      
   Rather than using BCP, you can use the T-SQL command to read the file.   
   This file requires that it is accessible from SQL Server. There is also   
   DTS, Data Transformation Services, which I have never used myself, but   
   which might be able to deal with more complex formats.   
      
   > Will the same file be usable in SQL 2005 Express as well as SQL 2000,   
   > without modification?   
      
   Yes. All I have said above applies to SQL 2005 as well, with one   
   modification: in SQL 2005 DTS was replaced by SQL Server Integration   
   Services. I don't think Express Edition comes with SSIS, though.   
      
   --   
   Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)