SQL Server to Flat File using SELECT INTO

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeV06 - 09 Jul 2008 00:41 GMT

This syntax works correctly with Access 2003 to dump a table to a flat
file.

SELECT * into [text;database=c:\].[tstjuly9#txt]
FROM tblInvoices
WHERE tblInvoices.StateOrProvince="VI" AND tblInvoices.CompanyID=10;

I have been trying to do the same thing using Query Analyzer on SQL Server
2000 without success.

Could someone please tell me the correct syntax for T-SQL on SQL Server
2000?

Thanks

Mike

Reply to this Message

JXStern - 09 Jul 2008 01:42 GMT

>This syntax works correctly with Access 2003 to dump a table to a flat
>file.
[quoted text clipped - 8 lines]
>Could someone please tell me the correct syntax for T-SQL on SQL Server
>2000?

No such, have to use SSIS or BCP to do the dump.

Or connect from Access and run your query - wish I'd thought of that a
while ago, could have used it!

Josh

Reply to this Message

Uri Dimant - 09 Jul 2008 06:07 GMT

Mike
You can create a linked server to the ACCESS file or if you are on SS2005 ,
why not using SSIS?

Or

INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="path where access is located";
User ID=Admin;Password='
)...tblname(col1,col2...)
SELECT col1,col2... FROM tblname

> This syntax works correctly with Access 2003 to dump a table to a flat
> file.
[quoted text clipped - 12 lines]
>
> Mike

Reply to this Message

JXStern - 09 Jul 2008 15:12 GMT

>Mike
>You can create a linked server to the ACCESS file or if you are on SS2005 ,
[quoted text clipped - 7 lines]
> )...tblname(col1,col2...)
>SELECT col1,col2... FROM tblname

Uri, I didn't know you could do that, CAN it also open a flatfile
datasource and directly output to it after all!!!??

Josh

>> This syntax works correctly with Access 2003 to dump a table to a flat
>> file.
[quoted text clipped - 12 lines]
>>
>> Mike

Reply to this Message

Uri Dimant - 09 Jul 2008 15:48 GMT

JXStern
I tried it only with ACCESS. Nowasays we have SSIS , but try the following

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1
varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
---- set @Text1= char(10)+@Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

>>Mike
>>You can create a linked server to the ACCESS file or if you are on SS2005
[quoted text clipped - 31 lines]
>>>
>>> Mike

Reply to this Message

MikeV06 - 09 Jul 2008 16:38 GMT

Uri, I am trying to use the select clause to write to a flat file. That
insert takes me to a Access db, correct? I understand about linked server
and SSIS. However, I was trying to get the select clause to work also
(which also puts column heads in the flat file). Also, I use to be able to
do this with query analyzer outputting to a file.

Thanks

Mike

> Mike
> You can create a linked server to the ACCESS file or if you are on SS2005 ,
[quoted text clipped - 24 lines]
>>
>> Mike

Reply to this Message