数据库实用大全（四）

 -以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。
--SET LANGUAGE { [ N ] 'language' | @language_var } 

USE master

--设置会话的语言环境为: English
SET LANGUAGE N'English'
SELECT 
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果:
Month Weekday CONVERT
------------- -------------- -------------------------------
March Tuesday Mar 15 2005 8:59PM
--*/

--设置会话的语言环境为: 简体中文
SET LANGUAGE N'简体中文'
SELECT 
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果
Month Weekday CONVERT
------------- --------------- -----------------------------------------
05 星期四 05 19 2005 2:49:20:607PM
DECLARE @dt datetime
SET @dt=GETDATE()

--1．短日期格式：yyyy-m-d,把后面的“-0”换成‘-’，N是什么意思没有懂
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')

--2．长日期格式：yyyy年mm月dd日
--A. 方法
--select STUFF('123456',2,3,2233);结果：;从第二个位置开始，向后数到第三个位置，还成.
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
--B. 方法
SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'

--3．长日期格式：yyyy年m月d日
SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'

--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
2、SQL连接EXCEL查EXCEL数据
select * from opendatasource('Microsoft.Ace.OleDB.12.0','data source=d:\10W.xlsx;extended properties=excel 12.0')...[sheet1$]  

3、把EXCLE的数据传到SQL，传的数据要建一个新表
2.3.1 OpenDataSource 方式
select * into [0111121] from opendatasource('Microsoft.Ace.OleDB.12.0','data source=d:\10W.xlsx;extended properties=excel 12.0')...[sheet1$]  
注：把d:\10W.xlsx, sheet1的信息放到SQL表[0111121]中，[0111121]为一个新表
下面这个一样的效果
select * from OpenDataSource
('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\10w.xls;Extended Properties="Excel 12.0;HDR=no;IMEX=1"')...[Sheet1$]
HDR=yes或NO表示第一列是否为行标。
2.3.2 OpenRowSet方式
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\10w.xls', 'select * from [Sheet1$]') 

select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\10w.xls', [Sheet1$])  

4、把EXCLE的数据写到SQL的一个表中（原表的值不变）
表的格式要求一样的
2.4.1 OpenRowSet方式
insert into [0111121] 
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\10w.xls', [Sheet1$])  
注：把d:\10W.xlsx, sheet1的信息追加到SQL表[0111121]中，[0111121]在原SQL中存在的。
2.4.2 opendatasource方式
insert into [0111121]
select * from opendatasource('Microsoft.Ace.OleDB.12.0','data source=d:\10W.xlsx;extended properties=excel 12.0')...[sheet1$]

5、数据库中导出、导入bcp（OK）
--从数据库中导出数据并存到文件中, c是字段类型，一定要小写的，大写的不对了。还有一定要放在共享文件夹下面。

EXEC master..xp_cmdshell 'bcp test.dbo.成绩out d:\test\123.txt -c -S"PC2011061511NRV" -U"sa" -P"30687724"'


--找出自己想要的行数据：要加QUERYOUT OR QUERYOUTIN
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.成绩" queryout d:\test\123.xls -c -S"PC2011061511NRV" -U"sa" -P"30687724"'

————————————————————————————————————————

--从文件中导入数据到数据库对应表中，-U用IP或用户名都可以。”-q”要不要都行，-q是若要指定包含空格或单引号的数据库名称，必须使用 -q 选项：
EXEC master..xp_cmdshell 'bcp test.dbo.成绩 in d:\test\123.xls -c –q -S"127.0.0.1" -U"sa" -P"30687724"'

注：以上方法对于XLSX（好像打不开，没有懂）、CSV（要分裂这个还没有搞明白）文件也是支持的。一般用TXT，小文件用XLS即可。复制50W也就5秒，快死了。

如果是TXT文件，最好格式要求为：“文本文件（制表符分隔）”而且原表的数据类型要求为“VARCHAR(50)”
EXEC master..xp_cmdshell 'bcp test.dbo.book1 in D:\test\yh_2010-03-2\2010-3-2\2010-3-2.txt -t"\t" -c -S"127.0.0.1" -U"sa" -P"30687724"'


如果有提示启用什么的输入以下：

--已测试EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

--未测试，网上的办法；--启用xp_cmdshell
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE 
--关闭xp_cmdshell 
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE

7、 BULK INSERT导入法
BULK INSERT test.dbo.book1 FROM 'D:\test\yh_2010-03-2\2010-3-2\2010-3-2.txt'  
WITH (  
  FIELDTERMINATOR = '\t',  
  ROWTERMINATOR = '\n'  
)
如果是TXT文件，最好格式要求为：“文本文件（制表符分隔）”