sql点滴

子表查询，父表一定要加别名，否则数据会有问题，不报编译错

select * from table1 a where exist(select 1 from table2 where a.id=b.id)

 

存储过程结果集插入到现有表

insert into table1 exec procname args

 

联合已有表，紧跟在后面，对汇总很有用

select a from table1 union all select '合计'

 

结果集里的某列相加

declare @var nvarchar(1000)
set @var=''
select @var=@var+','+alarmname from notice where args=@args

print @var

 

shell命令

declare @cmd nvarchar(1000)
set @cmd='bcp "select cellphone,msg from atm.dbo.sendoutmsg" queryout '+@outfile+' -c -q -t "|"  -U'+@sqluser+' -P'+@sqlpwd +' -S'+@server
EXEC master..xp_cmdshell    @cmd    ,NO_OUTPUT

(

@cmd 可以是：

dir    c:/      c:/dir_out.txt

del   c:/deposit.htm

copy   c:/resource/deposit.htm    c:/

move   c:/倒入0911.xls    d:/倒入0911.xls

)

 

大容量数据导入(以/r/n为数据行结尾,|为列分割符)

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT dbo.tmpbasedevinfo FROM ''D:/dev_bmsg.txt''

                             with (FIELDTERMINATOR =''|'',

                                      ROWTERMINATOR = '''+CHAR(10)+''')';
exec(@bulk_cmd);

 

判断文件是否存在

create table #tb(a bit,b bit,c bit)  
  insert into #tb exec master..xp_fileexist 'c:/furniture.txt'
  if exists(select * from #tb where a=1)
  PRINT 文件存在

  else

  PRINT 文件不存在
drop table #tb