纳金网

标题: 常用sql笔记 [打印本页]

作者: 烟雨 时间: 2015-12-25 06:52
标题: 常用sql笔记

1、查询锁住的表，并删除
select
request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id ) tableName
from
sys.dm_tran_locks
where
resource_type='OBJECT'
KILL 107--spid

2、跨数据库，将表A插到表B
SET IDENTITY_INSERT [dbo]. T_Bas_Enterprise ON
INSERT INTO dbo. T_Bas_Enterprise
      ( PK_UCode ,
      PK_EntID,
      EntCode ,
      UnitName ,
      ShortName ,
      UCPinyin ,
      Leager ,
      LeagerPhone ,
      Licence ,
      FK_EntTypeID ,
      FK_CTCode ,
      FK_VTCode ,
      FK_ESCode ,
      FK_RTCode ,
      FK_ERCode ,
      LinkName ,
      LinkPhone ,
      LinkMPhone ,
      LinkMail ,
      Fax ,
      PostCode ,
      PPDate ,
      Address ,
      EntPic ,
      PosX ,
      PosY ,
      fbitIsAnnex ,
      fbitStop ,
      CreateDate ,
      Creater ,
      UpdateDate ,
      Updater
      )
SELECT
   NEWID() PK_UCode,PK_EntID ,EntCode, UnitName,ShortName ,UCPinyin, Legaler,BPhone LeagerPhone,NULL Licence,
   NULL fk_entTypeID,FK_CTCode ,FK_VTCode, FK_ESCode,FK_RTCode ,FK_ERCode ,
   LinkName,Phone linkphone, Mobile linkmphone ,EMail linkMail,Fax ,PostCode, PPDate,Address ,
   EntPic,PosX ,PosY, fbitIsAnnex,fbitStop ,fdtmCreateDate CreateDate,fdtmCreateUser Creater,
   fdtmCreateDate updateteDate ,fdtmCreateUser updater
FROM [192.168.253.63] .[DB_JJDC]. [CSBAS].[T_Bas_Enterprise]

3、当前数据库中，将表A插入到表B
insert into a (a的字段 ) select * form b
a的字段与*字段要一致

4、将List 以指定字符连接成一个字符串  如  1、2、 3
List<string> list = new List<string>();
         var str = list.Aggregate((p, next) => (p + "-" + next));

欢迎光临纳金网 (http://go.narkii.com/club/)