EXCEL中CONCATENATE()函数的使用

在EXCEL中可以使用CONCATENATE()函数进行字符串的拼接。或者合成SQL插入语句。

下面举例说明EXCEL中利用CONCATENATE()函数生成数据库的insert脚本

1、准备EXCEL数据，如图所示

序号	姓名	语文	数学	英语	物理	化学	生物
1	张珊	100	101	102	103	90	60
2	李四	101	102	103	104	89	59
3	王二	102	103	104	105	88	58

2、在数据库中创建表结构

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblScore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
-- 删除表
drop table [dbo].tblScore
go

create table dbo.tblScore (
	Id int not null
	, Name nvarchar(255) null
	, Score1 int null 
	, Score2 int null
	, Score3 int null
	, Score4 int null
	, Score5 int null
	, Score6 int null
)

go

3、在EXCEL中生成插入语句

=CONCATENATE("Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '",A2,"',N'",B2,"','",C2,"','",D2,"','",E2,"','",F2,"','",G2,"','",H2,"')  ")

=CONCATENATE("Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '",A3,"',N'",B3,"','",C3,"','",D3,"','",E3,"','",F3,"','",G3,"','",H3,"')  ")

=CONCATENATE("Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '",A4,"',N'",B4,"','",C4,"','",D4,"','",E4,"','",F4,"','",G4,"','",H4,"')  ")

Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '1',N'张珊','100','101','102','103','90','60')  
Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '2',N'李四','101','102','103','104','89','59')  
Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '3',N'王二','102','103','104','105','88','58')  

4、在数据库中插入数据并查询验证

完整代码如下：

use Test
go

---------- create table Score ----------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblScore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
-- 删除表
drop table [dbo].tblScore
go

create table dbo.tblScore (
	Id int not null
	, Name nvarchar(255) null
	, Score1 int null 
	, Score2 int null
	, Score3 int null
	, Score4 int null
	, Score5 int null
	, Score6 int null
)

go
---------- insert data ----------


Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '1',N'张珊','100','101','102','103','90','60')  
Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '2',N'李四','101','102','103','104','89','59')  
Insert into tblScore ( Id,Name,Score1,Score2,Score3,Score4,Score5,Score6 ) Values ( '3',N'王二','102','103','104','105','88','58')  

select * from dbo.tblScore