sql - SSMS:如何从 excel 导入(复制/粘贴)数据
问题描述
如何从 excel 复制/粘贴以下分隔数据(默认用制表符分隔):
declare @t_values nvarchar(max) =
N'
NULL 490366 NULL NULL
NULL 490400 NULL NULL
NULL 490402 NULL NULL
483061 490404 10 abc1
NULL 490406 NULL NULL
9766167 490408 3 abc2'
;
到我的临时表:
CREATE TABLE #insertTable
( transaction_id int
,user_id int
,purchase_price decimal(8,2)
,mess varchar(8)
);
?
注意!不使用OPENROWSET
解决方案INSERT INTO
:
- 询问
- 程序
- SNIPPET (在这里创建片段的快速方法)
解决方案SELECT INTO
:
SNIPPET (在这里创建片段的快速方法)
PS如果你有旧的 SQL Server 版本(数据库兼容性设置在 130 以下),你将需要单独的STRING_SPLIT
功能。我推荐以下解决方案。
解决方案
请使用以下 PROC:
CREATE PROCEDURE dbo.p_values2insertquery
@t_name nvarchar(128), -- Destination Table Name + [(column names)]
@t_values nvarchar(max), -- Paste Table Values here (from excel for instance)
@dlm nvarchar(128) = N' ' -- Delimiter: HT = HorizontalTab = char(9)
AS
/* p_values2insertquery
- converts table values stored in @t_values string (separated by default by @dlm=TAB)
to queries for insert to table name defined in @t_name
INPUT:
@t_name nvarchar(128), -- Destination Table Name + [(column names)]
@t_values nvarchar(max), -- Paste Table Values here (from excel for instance)
@dlm nvarchar(128) = N' ' -- Delimiter: HT = HorizontalTab = char(9)
-- NB! Script do NOT write any ifo to any table
-- to perform insert please copy/paste script output and execute
EXAMPLE:
IF OBJECT_ID('tempdb..#insertTable') IS NOT NULL DROP TABLE #insertTable;
CREATE TABLE #insertTable
( transaction_id int
,user_id int
,purchase_price decimal(8,2)
,mess varchar(8)
);
declare @t_name nvarchar(128) = '#insertTable' -- Table Name for insert
declare @t_values nvarchar(max) = -- Paste Table Values here (from excel for instance)
N'
NULL 490366 NULL NULL
NULL 490400 NULL NULL
NULL 490402 NULL NULL
483061 490404 10 abc1
NULL 490406 NULL NULL
9766167 490408 3 abc2'
;
declare @dlm nvarchar(128) = char(9); -- Delimiter: HT = char(9) = HorizontalTab
exec p_values2insertquery @t_name, @t_values
-- Output:
insert into #insertTable VALUES(NULL,'490366',NULL,NULL);
insert into #insertTable VALUES(NULL,'490400',NULL,NULL);
insert into #insertTable VALUES(NULL,'490402',NULL,NULL);
insert into #insertTable VALUES('483061','490404','10','abc1');
insert into #insertTable VALUES(NULL,'490406',NULL,NULL);
insert into #insertTable VALUES('9766167','490408','3','abc2');
*/
select N'insert into '
+ @t_name -- table name
+ ' VALUES('
+ replace -- replace 'NULL' by NULL
( ''''
+ replace -- surround values by quotes 'value1','value2'...
( value
, @dlm -- Delimiter: HT = char(9) = HorizontalTab
, ''','''
)
+ ''''
, '''NULL'''
, 'NULL'
)
+ ');' as insertQuery
--into #t
from string_split -- insert Line per Row into table
( replace -- replace CR + LF by LF (CR - Carriage Return, LF - Line Feed)
( @t_values -- Paste Table Values here (from excel for instance)
, char(13)+char(10) -- CR + LF
, char(10) -- LF
)
,char(10)
)
where len(value)>0 -- skip empty rows
;
/* the same, but step by step (for explanatory purposes):
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
select @t_values=replace(@t_values,char(13)+char(10), char(10)) ; -- replace CR + LF by LF (CR - Carriage Return, LF - Line Feed)
select value into #t from string_split(@t_values,char(10)) ; -- insert Line per Row into table
delete from #t where len(value)<1 ; -- delete empty lines
update #t set value = '''' + replace(value, @dlm, ''',''') + ''''; -- surround values by quotes 'value1','value2'...
update #t set value = replace(value, '''NULL''', 'NULL'); -- replace 'NULL' by NULL
update #t set value = N'insert into '+ @t_name + ' VALUES(' + value + ');'
select * from #t
*/
RETURN 0
例子:
IF OBJECT_ID('tempdb..#insertTable') IS NOT NULL DROP TABLE #insertTable;
CREATE TABLE #insertTable
( transaction_id int
,user_id int
,purchase_price decimal(8,2)
,mess varchar(8)
);
declare @t_name nvarchar(128) = '#insertTable' -- Table Name for insert
declare @t_values nvarchar(max) = -- Paste Table Values here (from excel for instance)
N'
NULL 490366 NULL NULL
NULL 490400 NULL NULL
NULL 490402 NULL NULL
483061 490404 10 abc1
NULL 490406 NULL NULL
9766167 490408 3 abc2'
;
declare @dlm nvarchar(128) = char(9); -- Delimiter: HT = char(9) = HorizontalTab
exec p_values2insertquery @t_name, @t_values
输出(只需复制/粘贴并执行以执行插入):
insert into #insertTable VALUES(NULL,'490366',NULL,NULL);
insert into #insertTable VALUES(NULL,'490400',NULL,NULL);
insert into #insertTable VALUES(NULL,'490402',NULL,NULL);
insert into #insertTable VALUES('483061','490404','10','abc1');
insert into #insertTable VALUES(NULL,'490406',NULL,NULL);
insert into #insertTable VALUES('9766167','490408','3','abc2');
推荐阅读
- javascript - 时刻UTC时间如何转换为秒
- python - Python将文件从当前路径移动到与被移动文件类似或相似的特定文件夹
- amazon-web-services - 使用 lambda 目的地时是否需要私有链接
- java - java 8中的内存区方法区、字符串常量池位于哪个内存区?
- 3d - 添加 3D 模型:支持哪些类型的文件?以及在哪里托管 3D 文件?
- typescript - 如何使用 Typescript 在 Codepen 中制作 Vuetify 组件?
- typescript - Typescript 编译器不强制执行相关的泛型
- python - POST 表单提交后下载文件
- java - 是否可以以编程方式滚动到 appbarlayout 的子级?
- excel - 将 .Find 结果分配给变量还是直接将其写入单元格?