import a text file into the database

hello,
i want to import a textfile in the database (SQL)
For access.database the code like:
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My Documents\TextFiles].[Sample2.txt]"
cnn.Execute sqlString
works ok
How can i use such a command for the sql server
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My Documents\TextFiles].[Sample2.txt]"
i get the error
"Invalid object name 'Text;HDR=NO;DATABASE=e:\My
Documents\TextFiles\Sample2.txt'."
best regardsYou can use a passthrough query like:
INSERT tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\My Documents\TextFiles].[Sample2.txt]";
User ID=Admin;Password=;Extended properties=text )
Another option is to use xp_cmdshell directly like:
INSERT tbl
EXEC master..xp_cmdshell 'TYPE e:\My Documents\TextFiles].[Sample2.txt]" ;
For complex ascii files you should consider using BULK INSERT/BCP IN or
DTS( Data transformation Services ). See SQL Server Books Online for details
on these utilities.
Anith|||hello Anith,
i tryed to run in the query analyser the command
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\transfer\Input\t1.txt";Extended properties=text')
but i get the syntax error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
any idea?
thanks
Xavier
"Anith Sen" wrote:

> You can use a passthrough query like:
> INSERT tbl
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="e:\My Documents\TextFiles].[Sample2.txt]";
> User ID=Admin;Password=;Extended properties=text )
> Another option is to use xp_cmdshell directly like:
> INSERT tbl
> EXEC master..xp_cmdshell 'TYPE e:\My Documents\TextFiles].[Sample2.txt]" ;
> For complex ascii files you should consider using BULK INSERT/BCP IN or
> DTS( Data transformation Services ). See SQL Server Books Online for detai
ls
> on these utilities.
> --
> Anith
>
>