SSMS:如何从excel导入(复制/粘贴)数据
SSMS:如何从excel导入(复制/粘贴)数据
提问于 2020-04-03 13:05:29
如何从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)
);NB！不使用OPENROWSET
INSERT INTO的解决方案
片段的方法)
SELECT INTO的解决方案
片段的方法)
P.S.如果您有旧的Server版本(数据库兼容性集在130以下)，您将需要单独的STRING_SPLIT函数。我建议您遵循solution。
回答 5
Stack Overflow用户
回答已采纳
发布于 2020-04-06 04:49:05
请试如下：
-- NB! Script do NOT write any ifo to any table
-- to perform insert please copy/paste script output to the @tbl variable and execute
declare @tbl_name nvarchar(128) = '#insertTable'; -- Destination Table Name + [(column names)]
declare @dlm nvarchar(128) = char(9); -- Delimiter: HT = char(9) = HorizontalTab
declare @tbl 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'
;
select N'insert into '
+ @tbl_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 fn_split_string -- insert Line per Row into table
( replace -- replace CR + LF by LF (CR - Carriage Return, LF - Line Feed)
( @tbl -- 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 rowsStack Overflow用户
发布于 2020-04-03 13:05:29
请使用以下程序：
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');Stack Overflow用户
发布于 2020-04-03 13:11:34
如果出于某些原因不想使用PROC (例如，没有权限)，则可以使用代码段。
请将此代码保存为扩展名为.snippet的文件，并通过Code Snippets Manager将其添加到您的代码段中。
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>InsertTable</Title> <!--InsertTable.snippet -copy/paste for filename-->
<Shortcut></Shortcut>
<Description>Prepare Copy/Paste Excel data for inserting to Table</Description>
<Author>Denis Sipchenko</Author>
<SnippetTypes>
<SnippetType>SurroundsWith</SnippetType> <!--SurroundsWith/Expansion-->
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal> <ID>t_name</ID> <Default>'#insertTable'</Default> <ToolTip>Destination Table Name [(column names)]</ToolTip> </Literal>
<Literal> <ID>t_values</ID> <Default></Default> <ToolTip>copy/paste Excel data here</ToolTip> </Literal>
<Literal> <ID>delimiter</ID> <Default>char(9)</Default> <ToolTip>Column Delimiter: HT = char(9) = HorizontalTab</ToolTip> </Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[-- NB! Script do NOT write any ifo to any table
-- to perform insert please copy/paste script output and execute
declare @tbl_name nvarchar(128) = $t_name$; -- Destination Table Name + [(column names)]
declare @dlm nvarchar(128) = $delimiter$; -- Delimiter: HT = char(9) = HorizontalTab
declare @tbl nvarchar(max) = -- Paste Table Values here (from excel for instance)
N'$t_values$'
;
select N'insert into '
+ @tbl_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)
( @tbl -- 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
$selected$$end$
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>屏幕：
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61012585
复制相关文章
相似问题
腾讯云开发者
