1

I imported an Excel (.xlsx) file into a table in SQL Server using the import wizard.

I want to get the query used to import so that I can store it and incorporate it in a SQL Server stored procedure. How can I get that query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yash
  • 319
  • 1
  • 4
  • 6
  • 2
    The wizard will actually generate a SSIS package behind the scenes for the import - there is no one "query". You have the option to save the package if you wish to reuse it at a later date. – SMor Jun 25 '21 at 20:12

1 Answers1

0

You can't get the specific query used by the wizard, you can only save the SSIS package for later use. You can do this with other methods, via sql, which you can include in your stored procedure as explained in the official documentation.

OPENROWSET

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

BULK INSERT

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO
Stefano Sansone
  • 2,377
  • 7
  • 20
  • 39