SQL query from import wizard

Question

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?

Answer 1

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