How to use OpenRowSet to insert data into a blank file?

Question

How to use OpenRowSet to insert data into a blank file?

I need to insert into a txt file (say to D:\TDB) some select output (say select * from sys.tables) from the database

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\TDB;HDR=Yes;', 'SELECT * FROM sys.tables.txt')
select * from sys.tables;

I get

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'sys.tables.txt'. Make sure the object exists and that you spell its name and the path name correctly.".

Msg 7350, Level 16, State 2, Line

1 Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

What is wrong?

PS. please do not propose the bcp solution, cause already tested and does not work everytime, so I would test openrowset now..

Answer 1

@serhio , I tested your sql below：

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\TDB;HDR=Yes;', 'SELECT * FROM sys.tables.txt')
select * from sys.tables;

I got a few test results

1. The filename should not include "." in it. (sys.tables.txt→systables.txt)
2. HDR(Header Row) can not be used here.(Remove it)
3. The txt file must exist.(Create it)
4. The first line in the txt file should be the all column name of your source data.

sql

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=D:\TDB;', 'SELECT * FROM systables.txt')
select * from sys.tables;

systables.txt

name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published,lob_data_space_id,filestream_data_space_id,max_column_id_used,lock_on_bulk_load,uses_ansi_nulls,is_replicated,has_replication_filter,is_merge_published,is_sync_tran_subscribed,has_unchecked_assembly_data,text_in_row_limit,large_value_types_out_of_row,is_tracked_by_cdc,lock_escalation,lock_escalation_desc