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

sujith kumar matharasi 351 Reputation points
2021-09-27T13:35:53.4+00:00

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 dbo.abc (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
    2021-09-28T19:09:34.563+00:00

    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
    2021-09-27T14:17:24.983+00:00

    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:

    set ANSI_NULLS ON
    GO
    set QUOTED_IDENTIFIER ON
    GO
    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 dbo.abc
    exec(@dbfile)
    
    go
    

    Dont forget you need to have installed this engine:
    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    And also run these on the instance after:

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

  2. Erland Sommarskog 106K Reputation points MVP
    2021-09-27T21:53:36.217+00:00

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

    SELECT @sql = col FROM tbl WHERE ...
    EXEC(@sql)
    

    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
    2021-09-28T01:49:15.423+00:00

    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.

    135639-screenshot-2021-09-28-094657.jpg

    Regards,

    Zoe


    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
    2021-09-27T15:31:33.31+00:00

    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?

    Thanks.

    0 comments No comments