数据库SQL

1.把一个数据库表中的数据插入到另外一个数据库中的一张表中

　　数据库表结构不同　　

INSERT INTO [CodeFirstDbContext].[dbo].[Companies](CompanyID,CompanyName,Manager,CompanyType,IsDelete)
SELECT CompID,Company,CName,ctype,0 FROM [YFDB2012].[dbo].[Company]

　　数据库表结构相同

INSERT INTO [CodeFirstDbContext].[dbo].[Companies] SELECT * FROM [YFDB2012].[dbo].[Company]

2.将一个表中的数据串成一个字符串，并分组

　SELECT PostId, (
		SELECT CAST(PostId AS VARCHAR)+','+AttachName+','+ type+','+ AttachUrl+'|'   FROM T_Group_AttachFile WHERE PostId =TGA.PostId FOR XML PATH('') 
) AS AttachFileList FROM T_Group_AttachFile AS TGA GROUP BY PostId

　参考资料：http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

3.查看Sql Server版本

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

　　

4.使用SELECT INTO导出数据

　　同一数据库

SELECT*INTO table2 FROM table1

　　不同数据库

USE db1
SELECT*INTO db2.dbo.table2 FROM table1

　　

5.参照一张表修改另外一张表的数据

	 UPDATE T_Group_AttachFile 
	 SET AttachName=T1.AttachName,
	 Type=T1.Type,
	 AttachUrl=T1.AttachUrl
	 FROM (SELECT * FROM dbo.Fn_GetImageTab(@attachStr) WHERE ID!=0) 
	 AS T1 INNER JOIN T_Group_AttachFile AS T2 ON T1.ID=T2.FileId 

6.利用CTE查询树形结构，从某个根节点到子节点

with cte as
(
SELECT GroupId,PGroupId,GroupName,0 as lvl FROM T_Group_Info WHERE GroupId=29
union all
select d.GroupId,d.PGroupId,d.GroupName,lvl+1 from cte c inner join T_Group_Info d
on c.GroupId = d.PGroupId  
)
select * from cte

　　

7.定义表变量

DECLARE @temp TABLE(
    Id INT,
    Col1 NVARCHAR(20)
);

　　

 8.MD5加密

加密字符串
SELECT substring(sys.fn_VarBinToHexStr(hashbytes('MD5','123456')),3,32)
对字段加密
SELECT substring(sys.fn_VarBinToHexStr(hashbytes('MD5',CAST(password AS VARCHAR))),3,32) FROM UserInfo 

　　

 9.将exec执行的结果存入变量中

declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from '+@servername+'.a.dbo.b'
exec sp_executesql @sqls,N'@a int output',@num output
select @num

参考资料http://blog.chinaunix.net/uid-20125591-id-1734313.html

http://blog.csdn.net/u011955860/article/details/51980026