sys.syscolumns (Transact-SQL)

为每个表和视图中的每列返回一行，并为数据库中的存储过程的每个参数返回一行。

sysobjects

在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。

执行带嵌入参数的sql——sp_executesql

通常执行sql语句，大家用的都是exec，exec功能强大，但不支持嵌入参数，sp_executesql解决了这个问题。抄一段sqlserver帮助：






sp_executesql

执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。



语法

sp_executesql [@stmt =] stmt

[

{, [@params =] N'@parameter_name data_type [,...n]' }

{, [@param1 =] 'value1' [,...n] }

]



参数

[@stmt =] stmt



包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符串联两个字符串）。不允许使用字符常量。如果指定常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。



stmt 可以包含与变量名形式相同的参数，例如：



N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'



stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。



[@params =] N'@parameter_name data_type [,...n]'



字符串，其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。



[@param1 =] 'value1'



参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数，则不需要值。



n



附加参数的值的占位符。这些值只能是常量或变量，而不能是更复杂的表达式，例如函数或使用运算符生成的表达式。



返回代码值

0（成功）或 1（失败）



结果集

从生成 SQL 字符串的所有 SQL 语句返回结果集。





例子（感谢邹建提供）





declare @user varchar(1000)declare @moTable varchar(20)select @moTable = 'MT_10'declare @sql nvarchar(4000) --定义变量,注意类型set @sql='select @user = count(distinct userid) from '+@moTable --为变量赋值--执行@sql中的语句exec sp_executesql @sql ,N'@user varchar(1000) out' --表示@sql中的语句包含了一个输出参数 ,@user out --和调用存储过程差不多,指定输出参数值print @user





本例中，@moTable 为嵌入参数。


作者Blog：http://blog.csdn.net/kunp/

如何在XML中表示书名号？例子如下：

第一本书<>

SQL中时间和字符串函数

字符串函数（一） – MSSQL




字符串函数对二进制数据、字符串和表达式执行不同的运算。此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。常用的字符串函数有：



1、字符转换函数

有以下几种字符转换函数：

·ASCII（）

ASCII（） 函数返回字符表达式最左端字符的ASCII 码值。ASCII（） 函数语法如下：



提醒：在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。



CHAR（）

CHAR（） 函数用于将ASCII 码转换为字符。其语法如下：



如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 函数会返回一个NULL 值。



·LOWER（）

LOWER（） 函数把字符串全部转换为小写，其语法如下：



UPPER（）

UPPER（） 函数把字符串全部转换为大写，其语法如下：



·STR（）

STR（） 函数把数值型数据转换为字符型数据，其语法如下：

STR （[，length[， ]]）

自 变量length 和decimal 必须是非负值，length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。 小数位数大于decimal 值时，STR（） 函数将其下一位四舍五入。指定长度应大于或等于数字的符号位数 小数点前的位数 小数点位数 小数点后的位数。如果小数点前的位数超过了指定的长度，则返回指定长度的“*”。



2、去空格函数

去空格函数有如下两种：

·LTRIM（）

LTRIM（） 函数把字符串头部的空格去掉，其语法如下：

LTRIM （）



·RTRIM（）

RTRIM（） 函数把字符串尾部的空格去掉，其语法如下：

RTRIM （）



提示：在许多情况下，往往需要得到头部和尾部都没有空格字符的字符串，这时可将上两个函数嵌套使用。



3、取子串函数

取子串函数如下所示：

·LEFT（）

LEFT（） 函数返回部分字符串，其语法如下：

LEFT （， ）

LEFT（） 函数返回的子串是从字符串最左边起到第integer_expression 个字符的部分。若integer_expression 为负值，则返回NULL 值。



·RIGHT（）

RIGHT（） 函数返回部分字符串，其语法如下：

RIGHT （， ）

RIGHT（） 函数返回的子串是从字符串右边第integer_expression 个字符起到最后一个字符的部分。若integer_expression 为负值，则返回NULL 值。



·SUBSTRING（）

SUBSTRING（） 函数返回部分字符串，其语法如下：

SUBSTRING （， ， length）

SUBSTRING（） 函数返回的子串是从字符串左边第starting_ position 个字符起length个字符的部分。其中表达式可以是字符串或二进制串或含字段名的表达式。SUBSTRING（）函数不能用于TEXT 和IMAGE 数据类型。



4、字符串比较函数

字符串比较函数如下所示：

·CHARINDEX（）

CHARINDEX（） 函数返回字符串中某个指定的子串出现的开始位置，其语法如下：

CHARINDEX （<’substring_expression’>， ）

其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。



PATINDEX（）

PATINDEX（） 函数返回字符串中某个指定的子串出现的开始位置，其语法如下：

PATINDEX （<’%substring _expression%’>， ）

其中子串表达式前后必须有百分号“%”否则返回值为0。



与CHARINDEX（） 函数不同的是，PATINDEX（） 函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。



·SOUNDEX（）

SOUNDEX（） 函数返回一个四位字符码，其语法如下：

SOUNDEX （）

SOUNDEX （） 函数将character _expression 转换为4 个字符的声音码。其中第一个码为原字符串的第一个字符，第二到第四个字符则为数字，是该字符串的声音字母所对应的数字，但是忽略了除首字母外的串中的所有 元音字母 。SOUNDEX（） 函数可用来查找声音相似的字符串。但SOUNDEX 函数对数字和汉字均只返回0 值。



·DIFFERENCE（）

DIFFERENCE（） 函数返回由SOUNDEX（） 函数返回的两个字符表达式的值的差异。其语法如下：

DIFFERENCE （， ）

值的差异是用0、 1、 2、 3、 4 来表示的，含义如下：

0–两个SOUNDEX（） 函数返回值的第一个字符不同

1–两个SOUNDEX（） 函数返回值的第一个字符相同

2–两个SOUNDEX（） 函数返回值的第一、二个字符相同

3–两个SOUNDEX（） 函数返回值的第一、二、三个字符相同

4–两个SOUNDEX（） 函数返回值完全相同



5、字符串操作函数

字符串操作函数如下所示：

·QUOTENAME（）

QUOTENAME（） 函数返回被特定字符括起来的字符串，其语法如下：

QUOTENAME （<’character_expression’>[， quote_ character]）

其中quote_ character 标明括字符串所用的字符，如“‘”、“（”、“[” 等，缺省值为“[]”



·REPLICATE（）

REPLICATE（） 函数返回一个重复character_expression 指定次数的字符串，其语法如下：

REPLICATE （character_expression integer_expression）

如果integer_expression 值为负值，则REPLICATE（） 函数返回NULL 串。



·REVERSE（）

REVERSE（） 函数将指定的字符串的字符排列顺序颠倒，其语法如下：

REVERSE （）

其中character_expression 可以是字符串、常数或一个列的值。



·REPLACE（）

REPLACE（） 函数返回被替换了指定子串的字符串，其语法如下：

REPLACE （， ， ）

REPLACE（） 函数用string_expression3 替换在string_expression1 中的子串string_expression2。



·SPACE（）

SPACE（） 函数返回一个有指定长度的空白字符串，其语法如下：

SPACE （）

如果integer_expression 值为负值，则SPACE（） 函数返回NULL 串。



·STUFF（）

STUF（）函数用另一子串替换字符串指定位置、长度的子串，其语法如下：

STUFF （， ， ，）

如 果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则STUFF（） 函数返回NULL 值。如果length 长度大于character_expression1 的长度，则character_expression1 只保留首字符。



6、数据类型转换函数

在一般情况下，SQL Server 会自动完成数据类型的转换，例如，可以直接将字符数据类型或表达式与DATATIME 数据类型或表达式比较当表达式中用了INTEGER、 SMALLINT或TINYINT 时，SQL Server 也可将INTEGER 数据类型或表达式转换为SMALLINT数据类型或表达式，这称为隐式转换。如果不能确定SQL Server 是否能完成隐式转换或者使用了不能隐式转换的其它数据类型，就需要使用数据类型转换函数做显式转换了。此类函数有两个：

·CAST（）

CAST（） 函数语法如下：

CAST （ AS [ length ]）



·CONVERT（）

CONVERT（） 函数语法如下：

CONVERT （[ length ]， [， style]）



提醒：1、data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。

2、length用于指定数据的长度，缺省值为30。

3、把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号（ ）或负号（-）的数值。

4、TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。

5、IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。

6、把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。

7、BIT类型的转换把非零值转换为1，并仍以BIT类型存储。

8、试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“ ”，以标识发生了这种截断。



用CONVERT （） 函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。如表4-8 所示。



7、日期函数

日期函数用来操作DATETIME 和SMALLDATETIME 类型的数据，执行算术运算。与其它函数一样，可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用日期函数。其使用方法如下：

日期函数参数，其中参数个数应不同的函数而不同。



·DAY（）

DAY（） 函数语法如下：

DAY （）

DAY（） 函数返回date_expression 中的日期值。



在上例中，DAY（） 函数使用整数值作参数，系统从1900 年1 月1 日起往后数指定整数值 1， 天再返回其日期。



MONTH（）

MONTH（） 函数语法如下：

MONTH ()

MONTH（） 函数返回date_expression 中的月份值。



与DAY（） 函数不同的是，MONTH（） 函数的参数为整数时，一律返回整数值1，即SQL Server 认为其是1900 年1 月。



·YEAR（）

YEAR（） 函数语法如下：

YEAR （）

YEAR（） 函数返回date_expression 中的年份值。



提醒：在使用日期函数时，其日期值应在1753年到9999年之间，这是SQL Server系统所能识别的日期范围，否则会出现错误。



·DATEADD（）

DATEADD（） 函数语法如下：

DATEADD （， ， ）

DATEADD（） 函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期。参数“datepart” 在日期函数中经常被使用，它用来指定构成日期类型数据的各组件，如年、季、月、日、星期等。其取值如表4-9 所示：



·DATEDIFF（）

DATEDIFF（） 函数语法如下：

DATEDIFF（） （， ， ）

DATEDIFF （） 函数返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。针对不同的datepart， DATEDIFF（）函数所允许的最大差距值不一样，如：datepart 为second 时，DATEDIFF（） 函数所允许的最大差距值为68： 年datepart 为millisecond 时，DATEDIFF（） 函数所允许的最大差距值为24 天20 小时30 分23 秒647 毫秒。



·DATENAME（）

DATENAME（） 函数语法如下：

DATENAME （，

DATENAME（） 函数以字符串的形式返回日期的指定部分此部分。由datepart 来指定。



·DATEPART（）

DATEPART（） 函数语法如下：

DATEPART （， ）

DATEPART（） 函数以整数值的形式返回日期的指定部分。此部分由datepart 来指定。

DATEPART （dd， date） 等同于DAY （date）

DATEPART （mm， date） 等同于MONTH （date）

DATEPART （yy， date） 等同于YEAR （date）



·GETDATE（）

GETDATE（） 函数语法如下：

GETDATE（）

GETDATE（） 函数以DATETIME 的缺省格式返回系统当前的日期和时间，它常作为其它函数或命令的参数使用。

使用T-SQL导入多个文件数据到SQL Server中

使用T-SQL导入多个文件数据到SQL Server中




在我们的工作中，经常需要连续输入多个文件的数据到SQL Server的表中，有时需要从相同或者不同的目录中，同时将文件中的数据倒入。在这篇文章中，我们将讨论如何同时把一个目录中的文件的数据倒入到SQL Server中。



试验环境

我们先创建整个试验的环境。创建文件目录“C:\MyImport”，和三个文件a.csv、b.csv和c.csv，文件内容如下。同时，在SQL Server中创建一个表用来存放导入的数据。

C:\MyImport\a.csv

1, MAK, A9411792711, 3400.25

2, Claire, A9411452711, 24000.33

3, Sam, A5611792711, 1200.34

C:\MyImport\b.csv

11, Rubon, 9671792711, 400.14

22, Mike, 9418952711, 4000.56

39, Hsu, 75611792511, 1230.00

C:\MyImport\c.csv

69, Lucy, 8411992710, 305.11

45, Grace, 3413452713, 246.52

33, Saint, 5461795716, 1278.70

Create Database Bank

Go

Use Bank

go

Create table Account

(

[ID] int, Name Varchar(100),

AccountNo varchar(100), Balance money

)

Go

Create table logtable

(

id int identity(1,1),

Query varchar(1000),

Importeddate datetime default getdate()

)



方法 1: XP_CMDSHELL 和BULK INSERT

这个方法使用xp_cmdshell和Bulk Insert的SQL命令把一个目录中的文件倒入到SQL Server的表中。



创建存储过程



在数据库中产生这个存储过程，这个存储过程有三个参数：文件路径，文件扩展名和数据库的表名。



Create procedure usp_ImportMultipleFiles @filepath varchar(500),

@pattern varchar(100), @TableName varchar(128)

as

set quoted_identifier off

declare @query varchar(1000)

declare @max1 int

declare @count1 int

Declare @filename varchar(100)

set @count1 =0

create table #x (name varchar(200))

set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'

insert #x exec (@query)

delete from #x where name is NULL

select identity(int,1,1) as ID, name into #y from #x

drop table #x

set @max1 = (select max(ID) from #y)

--print @max1

--print @count1

While @count1 <= @max1

begin

set @count1=@count1+1

set @filename = (select name from #y where [id] = @count1)

set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"

WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'

--print @query

exec (@query)

insert into logtable (query) select @query

end



drop table #y



执行

执行上面的存储过程，参数如下：

例1：输入所有的c:\myimport目录下的.csv文件到Account表中

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'



例2：输入所有的c:\myimport目录下的文件到Account表中

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.*', 'Account'



方法 2: XP_CMDSHELL 和 BCP 公用程序

这个方法使用 "xp_cmdshell" 和 "BCP.exe" 倒入一个目录下的文件到SQL Server的表中。这个存储过程以服务器名、数据库名、文件路径、文件扩展名和数据库表名作为参数。

注意：确信你运行SQL Server Agent的帐户具有访问你输入的文件夹和服务器的权限。



创建存储过程：

set quoted_identifier off

go

Create procedure usp_ImportMultipleFilesBCP @servername varchar(128),

@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100),

@TableName varchar(128)

as

declare @query varchar(1000)

declare @max1 int

declare @count1 int

Declare @filename varchar(100)

set @count1 =0

create table #x (name varchar(200))

set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'

insert #x exec (@query)

delete from #x where name is NULL

select identity(int,1,1) as ID, name into #y from #x

drop table #x

set @max1 = (select max(ID) from #y)

--print @max1

--print @count1

--select * from #y

While @count1 <= @max1

begin

set @count1=@count1+1

set @filename = (select name from #y where [id] = @count1)

set @Query ='bcp "'+ @databasename+'.dbo.'+@Tablename + '"

in "'+ @Filepath+@Filename+'" -S' + @servername + ' -T -c -r\n -t,'

set @Query = 'MASTER.DBO.xp_cmdshell '+ "'"+ @query +"'"

--print @query

EXEC ( @query)

insert into logtable (query) select @query

end



drop table #y



执行

执行上面的存储过程，参数如下：

例1：输入所有的c:\myimport目录下的.csv文件到Account表中

Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:\Myimport\','*.csv','Account'



例2：输入所有的c:\myimport目录下的文件到Account表中

Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:\Myimport\','*.*','Account'



结果

不管你使用方法一还是方法二，数据将会被导入到SQL Server的表中。如下所示：





1



MAK



A9411792711



3400.25



2



Claire



A9411452711



24000.33



3



Sam



A5611792711



1200.34



11



Rubon



9671792711



400.14



22



Mike



9418952711



4000.56



39



Hsu



75611792511



1230



69



Lucy



8411992710



305.11



45



Grace



3413452713



246.52



33



Saint



5461795716



1278.7





使用方法一产生的日志记录如下表：







1



BULK INSERT Account FROM "c:\myimport\a.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")



3/1/04 2:15



2



BULK INSERT Account FROM "c:\myimport\b.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")



3/1/04 2:15



3



BULK INSERT Account FROM "c:\myimport\c.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")



3/1/04 2:15





使用方法二产生的日志记录如下表：





1



MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\a.csv" -SSQL -T -c -r\n -t,'



3/1/04 2:18



2



MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\b.csv" -SSQL -T -c -r\n -t,'



3/1/04 2:18



3



MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\c.csv" -SSQL -T -c -r\n -t,'



3/1/04 2:18





总结

像前面提到的一样，这篇文章的目的是告诉你如何从一个文件夹中导入多个文件到SQL Server的表中。这些存储过程可以进一步增强，可以用xp_getfiledetails来判断转入的文件的最后修改时间来确定文件的范围。这些存储过程也可以创建为定时执行的作业。在导入完成以后，你可以用rename或move命令来重命名或者移到不同的目录中。



参考:



使用DTS导入多个文件数据到SQL Server中