sqlserver 操作技巧

1.将不同库中的一张表数据导入到另外一张表中去

① 两张表多存在实体，两表的字段相同，字段的顺序相同的话。
  insert into 表B  select * from 表A 
② 两张表多存在实体，两表的字段相同，字段的顺序不相同的话。
               表B字段
insert into 表B （ F1,F2,F3） select  F1,F2,F3 from 表A 

2.将int类型改为varchar类型并更新数据库

update SM_Class set yearStr=CONVERT(varchar(4),rxYear)+CONVERT(varchar(1),rxTerm)

3.如果插入数据时存在主键自增长，需要在插入数据之前加入一句话

 set IDENTITY_INSERT 表名 on; 

4.更改字段类型

alter table dbo.Question_H_Biological         alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_Chemical          alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_Chinese           alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_English            alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_Geography       alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_His                  alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_Math               alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_Physical           alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_H_Polity              alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Biological         alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Chemical         alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Chinese          alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_English           alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Geography      alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_His                 alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Math              alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Physical          alter column LastUpdateTeacherId varchar(50)
alter table dbo.Question_M_Polity             alter column LastUpdateTeacherId varchar(50)
alter table dbo.QuestionSubject                alter column LastUpdateTeacherId varchar(50)

新增一列

alter table 表名  add column_name type

删除一列

alter table 表名 drop column 列名

 

4.跨库查询

select a.f_name,a.f_dlh,b.className2,a.f_sysrxnf,c.Id,c.ClassFlag,c.EntryYear,
a.f_sex from bbez_student.dbo.T_student a,bbez_student.dbo.T_StuClass b,
ExaminationSystem.dbo.Class c
where a.f_dlh = b.stuDlh
and b.className2 = c.ClassFlag
and a.f_sysrxnf = b.stuXn
and a.f_sysrxnf = c.EntryYear

 

5.先增加一列，再多表关联更新该列的值

alter table ExamTask add BankName varchar(50)

Update ExamTask set ExamTask.BankName = bank_Bank.Title from bank_Bank inner join ExamTask on ExamTask.BankIds=bank_bank.Id

 

6.replace

update Student set StuNum =REPLACE(StuNum,'wjc','')

 

7.计算一张表的字段数目

select COUNT(a.name) from sys.all_columns a,sys.tables b
where a.object_id=b.object_id and b.name='ecm_doc'

 

8.计算字段长度，截取字符串

select LEN(id) from jc_class
select right(id,LEN(id)-5) from jc_class
update jc_class set SortOrder=right(id,LEN(id)-5)
update jc_class set classflag=right(id,LEN(id)-5)