How to load a column from Excel which has insert statement and input the data into the table ?

sujith kumar matharasi 351 Reputation points

Hi All,

I have ssis package which loads data from excel file into a sql table.

In the excel file a column has data such as

"Insert into (id1,id2,id3) values(1,2,3)"

How do i insert this column into a table so it excecutes the insert statement and inputs the data into abc table?

Thanks in advance for all your help.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,274 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,509 questions
0 comments No comments
{count} votes

Accepted answer
  1. sujith kumar matharasi 351 Reputation points

    Thanks a lot for your suggestions, i loaded the file into a staging table and then used a cursor to load into my final table. Below is the code:

    DECLARE @_sqlcommand varchar(511) DECLARE my_cursor CURSOR FOR SELECT Script from dbo.stg OPEN my_cursor; -- Perform the first fetch. FETCH NEXT FROM my_cursor INTO @_sqlcommand; -- Check @@Fetch _STATUS to see if there are any more rows to fetch. WHILE @@Fetch _STATUS = 0 BEGIN exec sp_executesql @_sqlcommand -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM my_cursor INTO @_sqlcommand ; END CLOSE my_cursor; DEALLOCATE my_cursor

6 additional answers

Sort by: Most helpful
  1. Stratos Matzouranis 36 Reputation points

    You can also use OPENROWSET() to read/insert data from excel and use a job for that.:

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\User1\Desktop\test.xlsx','select * from [Sheet1$]')

    In your case you could create an SQL Server Agent Job with this step as it is below:

    declare @dbfile varchar(2000);
    print @dbfile
    set @dbfile = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Users\User1\test.xlsx'''+','+'''select * from [Sheet1$]'''+')'
    print @dbfile
    insert into

    Dont forget you need to have installed this engine:

    And also run these on the instance after:

    sp_configure 'show advanced options', 1;
    sp_configure 'Ad Hoc Distributed Queries', 1;
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0′, N'AllowInProcess', 1
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0′, N'DynamicParameters', 1
    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 106K Reputation points MVP

    Once you have the row in a table, you can do:

    SELECT @sql = col FROM tbl WHERE ...

    There are a lot precautions here. First of all you need some error handling, as it is not unlikely that the SQL statement will fail for some reason.

    It is also extremely important that you run these statements with an account with limited permissions; limited to what is actually permitted to be in that Excel file. Just as a crude example, you run the package as sa, then the Excel file has:

    ALTER SERVER ROLE sysadmin ADD MEMBER MickeyMous

    And MickeyMouse is really taking over the server.

    1 person found this answer helpful.
    0 comments No comments

  3. ZoeHui-MSFT 34,996 Reputation points

    Hi @sujith kumar matharasi .

    Save the excel file as csv and then use Execute SQL Task.

    Use file connection as the SQLSourceType as shown below.




    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    1 person found this answer helpful.
    0 comments No comments

  4. sujith kumar matharasi 351 Reputation points

    Hello Stratos,

    Thanks a lot for the reply, looks like i dont have access to do this step. So i created an SSIS package to load the file into the table now can you please let me know how can i run a column which has an insert script and load that data into another table?


    0 comments No comments