Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1534

Re: SQL 2000

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SQL 2000 "Rick_Ch" <rick_cheetham@hotmail.com> - 2013-07-14 12:09 +0100
  Re: SQL 2000 Erland Sommarskog <esquel@sommarskog.se> - 2013-07-14 13:56 +0200
  Re: SQL 2000 rja.carnegie@gmail.com - 2013-07-14 05:06 -0700

csiph-web