Path: csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: SQL 2000 Date: Sun, 14 Jul 2013 13:56:37 +0200 Organization: Erland Sommarskog Lines: 49 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx05.eternal-september.org; posting-host="78e2ae7e6764121e217a035f19bdb315"; logging-data="14292"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+NSLEpow+FKIn+C2nHqYko" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:LAPDS5E0loKjeaTAkvFUeKoX0jE= Xref: csiph.com comp.databases.ms-sqlserver:1534 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