How to use OpenRowSet to insert data into an empty file?

Question

How to use OpenRowSet to insert data into an empty file?

I need to insert in a txt file (say 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 "Microsoft Jet database engine could not find object 'sys.tables.txt'. Make sure the object exists and that you have its name and the path name is correct. ".

Msg 7350, Level 16, State 2, Line

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

What's wrong?

PS. please do not offer a bcp solution because it is already tested and does not work every time, so I would test openrowset now.

Answer 1

@serhio, I checked 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 some test results

• The file name must not contain "." in that. (Sys.tables.txt → systables.txt)
• HDR (Header Row) cannot be used here. (Delete)
• The txt file must exist. (Create it)
• The first line in the txt file should be the entire 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