sql - Import Excel Data Into Temporary Table Without Using OLEDB in SQL Server
问题描述
I am trying to look for a way to import excel data into a temporary table without using OLEDB since I don't have OLEDB installed in SQL Server and I can not install it due to security restrictions. I am aware of below mentioned ways of doing it but this is not going to help me
Begin Tran
If OBJECT_ID('tempdb..#tblUserImport') IS NOT NULL
Begin
Drop table #tblUserImport
end
Create Table [dbo].[#tblUserImport]
(
id nvarchar(max) NULL,
Name nvarchar(max) NULL,
Job_Title nvarchar(max) NULL,
Work_Email nvarchar(max) NULL
)
INSERT INTO [dbo].[#tblUserImport]
SELECT id, Name, Job Title, Work Email
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel12.0;HDR=YES;Database=C:\Users\Desktop\Book2.xlsx', 'SELECT * FROM [Sheet1$]');
select * from [#tblUserImport]
Rollback Tran
I will get the below mentioned error if I execute the openrowset.
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
Is it possible to achieve it using Stored Procedure or any other way?
解决方案
使用表变量的超级 mcguyver 方式。使用 Excel 工作表上的 concat 函数为插入部分准备数据。
declare @temp1 table (id int identity(1,1),name varchar(255),job_title varchar(255),work_title varchar(255),work_email varchar(255));
insert into @temp1 (name,job_title,work_title,work_email)
values
('John','Electrician','level 3 lightning wizard','john@ex.com'),
('amy','Java Developer','Cyber Coffee Slinger','amy@ex.com');
推荐阅读
- c++ - 将结构实例传递给函数不会引发错误
- android - Android SQLite DB 哪些对象必须关闭
- wordpress - 问:如何在添加帖子时添加自己的属性?
- visual-studio - 如何在 Visual Studio 2019 中更改状态栏颜色?
- c# - C#核心无法从sqs中删除消息
- excel - 批量检查超链接的状态
- javascript - 如何在相同的字符串javascript之间获取一个字符串
- mysql - 绝对初学者,尝试在 MySQL 中限制和订购 DELETE/JOIN 查询
- javascript - 在不使用数组方法的情况下避免嵌套 for 循环
- r - 如何从日期框架然后回归?