常用sql语句

1.两张结构相同的表 导入数据

insert into tableA (字段1,字段2,字段3) 
select 字段1,字段2,字段3 from tableB

2.触发器加判断条件

USE [dbname]
GO
/****** Object:  Trigger [dbo].[[tri_table]]    Script Date: 2019/4/25 11:25:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tri_table]
   ON  [dbo].TableB
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	--新增
    if(exists(select 1 from inserted) and not exists(select 1 from deleted))
		begin 
            declare @RoleID int--根据部门设置角色
            IF EXISTS(SELECT 1 FROM inserted WHERE ORG_NAME='部门A')
				set @RoleID=1
            ELSE IF EXISTS(SELECT 1 FROM inserted WHERE ORG_NAME='部门B')
				set @RoleID=2
			insert into TableA(字段1,字段2,部门,角色编号)
			select inserted.字段1,inserted.字段2,inserted.部门,@RoleID from inserted
		end
	--删除
    if(not exists(select 1 from inserted) and exists(select 1 from deleted))
		begin
			delete TableA from TableA u,
			deleted d where u.Account=d.Account
		end
	--更新
    if(exists(select 1 from inserted) and exists(select 1 from deleted))
		begin
			update TableA set  字段1=i.字段1,字段2=i.字段2
			from inserted i where TableA.Account=i.Account
		end
END

3.自增列重新从x开始

dbcc checkident(TableName,reseed,x)--x为任意

4.多行记录拼成1行并用 “  ，” 隔开

select val=stuff( 
    (select ','+ pgNAME from ActInfo as b where b.ActivityID = a.ActivityID and  b.ActivityID=1 for xml path('')),1,1,'')
from ActInfo as a where a.ActivityID = 1  group by ActivityID


id  pgNAME   ->>>    val
1   张三              张三,李四,王五
1   李四
1   王五

5.查询所有表包含某个字段的sql

SELECT DISTINCT
t.table_name,
c.COLUMN_NAME,
t.TABLE_TYPE
FROM
information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON c.TABLE_NAME = t.TABLE_NAME
where c.COLUMN_NAME = 'CityID'  --是否包含CityID字段
ORDER BY t.TABLE_TYPE

6.查询某张表是否被存储过程或者视图使用

select distinct object_name(id) from syscomments 
where id in (select id from sysobjects where type in('V','P')) --V表示视图，P表示存储过程
and text like '%tablename%'--tablename为需要查询被引用的表名称

7.批量插入测试数据（主从表）

declare @i int
set @i=1
while @i<101
begin
	--主表
    insert into table_name1(a,b,c,d,e,f,g,h,i)
    values('字段a','字段b','字段c','字段d','字段e','字段f','字段g','字段h','字段i')
	--从表1
    --(select ident_current('table_name1')) 获取主表的自增主键
    --添加从表，从表测试数据来源从表数据，条件为a=1
	insert into table_name2(a,b,c,d)
	select (select ident_current('table_name1')),b,c,d from table_name2 where a=1
	--从表2
	insert into table_name3(a,b,c)
	select (select ident_current('table_name1')),b,c from table_name3 where a=1
	set @i=@i+1
end