[dba-SQLServer] FW: FW: Element K Journal article

someone correct me if i am wrong but I don't think it is possible to use a 
text file as the source for a select statement directly.  you have to use 
the bulk insert statement to insert into a table and then display it from 
there.

for example, i export the customers table in northwind database to a local 
csv file (with the header row and trailing row count stripped out, the file 
should only contain 91 records):

/* -- cut here -- */

use tempdb
go
set nocount on
go
CREATE TABLE [Customers] (
	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
,
	[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED
	(
		[CustomerID]
	)  ON [PRIMARY]
) ON [PRIMARY]
GO

BULK INSERT tempdb..Customers
   FROM 'c:\test.csv'
   WITH
      (
         FIELDTERMINATOR = '\t',
         ROWTERMINATOR = '\n'
      )

go
select * from customers;
go

drop table customers

go

/* -- cut here -- */

HTH
Billy



>From: "Susan Harkins" <ssharkins at bellsouth.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] FW: FW: Element K Journal article
>Date: Thu, 26 May 2005 08:53:43 -0400
>
>The following came from a reader -- the reader is trying to adapt an Access
>technique for SQL Server. Gustav was the technical end of this article and
>his initial suggestion was to omit the AS keyword, but that didn't work.
>
>Anybody else want to offer a suggestion?
>
>
>Hi Susan,
>
>I read with interest your articles each month that appear in Inside
>Microsoft Access.  Although I have been doing advanced programming in 
>Access
>for about 10 years, each month I learn a new trick or two.  Most of the 
>time
>it's from one of your articles.  I just finished reading "Retrieve data 
>from
>a text file without attaching to or importing the file".  I tried your
>examples and they work as listed.  I am actually trying to do the exact 
>same
>thing in SQL Server 2000 and also SQL Server CE.  I tried using the same
>examples that I used in Access but on the SQL IN clause
>
>select * from Test#csv] as t in '' [Text;DATABASE=E:\]
>
>
>
>I get this error message.
>
>"Incorrect syntax near the keyword 'in'."
>
>
>
>In the IN-Less clause,
>
>select *  from [Text;DATABASE=E:\Test.csv] as t
>
>
>
>I get this error message.
>
>"Invalid object name 'Text;DATABASE=E:\Test.csv'."
>
>
>
>  Is there a similar syntax that works in SQL Server?
>
>
>
>Thanks,
>
>
>
>Fred
>
>
>
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>