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

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

Re: SQL 2000

Show key headers only | View raw

On Sunday, 14 July 2013 12:09:39 UTC+1, Rick_Ch  wrote:
> 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?
> 
> Will the same file be usable in SQL 2005 Express as well as SQL 2000, 
> without modification?

I think you can use a "BULK INSERT" statement in Query Analyzer 
in either case.  As far as I remember, you can use comma-separated
data, but can't use quote marks or commas /in/ values.  The quotes
are treated as data and the commas are treated as breaks.  Or maybe
I've been doing it wrong for many years.

1, "This won't work, I think", 450
2, This is all that you can do, 500

You can also use "BULK INSERT" with a separate "format file" - or, it's
been a while since I did it, maybe you have to - and that is really 
difficult to get right.  I /think/ you can create a data table for the
data that you want to import and then use the "BCP" command-line utility
to create a "format file" for your data - BCP and BULK INSERT are quite
similar.  So maybe you can use fixed-length data fields instead.

  1That's better, probably.       450
  2But it looks funny, huh?       500

The up-to-date way to import file data is "DTS" in SQL Server 2000
and "SSIS" in 2005, which is mostly the same thing.  In the simplest
mode that you /could/ initiate from Query Analyzer - although you
aren't really supposed to and it isn't easy - a DTS "package"
is a graphically designed set of instructions to read data from a
specific file (if that's what you want to do) and insert it into a
specific table.  Commas in quoted fields are handled okay in this system.

In our office we are not allowed to use DTS or SSIS (!) and what I often
do instead, is use Microsoft Excel to construct a script containing simple
"INSERT" statements as the file format for importing data.

INSERT #table VALUES (1, 'This''ll work, try it and see!', 450)
INSERT #table VALUES (2, 'But it may be risky', 500)

Of course (in this case) you also have to create a temporary table 
called #table, and also copy the data from it to where you want to 
keep it.  (It doesn't have to be a temporary table, but I like to
do it that way.  In fact, I usually call the table just "#".)

The risk that I have in mind is of the form of interference with 
your system called "SQL Injection", in which the automatic processing
of data has an outcome of breaching the separation of data values
and Transact-SQL commands - so that either by accident, or by someone's
malicious intention, what is meant to be data is treated as a command
to execute instead.

Like in this cartoon.  <http://xkcd.com/327/>

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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