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

Question

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.

Answer 1

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

Answer 2

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

*/

Answer 3

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.

Answer 4

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.

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

Answer 5

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.