SqlServer的表操作
删除学生表
drop table student
统计一个表有多少列
例：统计“m_record_new”表有多少列
select count(syscolumns.name)
from syscolumns , sysobjects
where syscolumns.id = sysobjects.id
and sysobjects.name = 'm_record_new'
查询和更新一个列的不同数据
更新：在matsn上加一个R
update aoi_mat_lot set matsn = matsn+'R'
where MATSN in('G32992600','G32632271','G32632115'）
查询：
select * from aoi_mat_lot
where matsn in('G32992600R','G32632271R','G32632115R')
将一张表的数据插入到另一张表，表结构不一致
表结构相同情况下：
insert into InfoLog select * from Info
select * into temptable from table
表结构不同的情况下,我们只需要指定列就可以，info(有数据的表)表和infolog表（要插入数据的表）
insert into InfoLog(ID,[Name]) select ID,[Name] from Info
--将A表插入B表中，，去除两张 表中含有的重复数据
BEGIN TRAN
INSERT INTO AOI_WO_BOM
SELECT * from [172.30.1.133].[Cimes5PRDDB_CN].DBO.AOI_WO_BOM A where not exists(
SELECT * from AOI_WO_BOM B where A.WO_BOM_SID = B.WO_BOM_SID ) AND MATNR = '203801000060'
--将Employees表的数据插入到Employees2 当中，并去除重复的数据
INSERT INTO Employees2 (id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58)
SELECT id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58 FROM Employees
WHERE NOT EXISTS (
SELECT 1 FROM Employees2
WHERE Employees2.id = Employees.id
)
--效率最高
--将Employees 表的数据插入到Employees1 表，并去除重复数据
begin tran
MERGE Employees1 AS target
USING Employees AS source
ON (target.id = source.id )
WHEN NOT MATCHED BY target THEN
INSERT (id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58)
VALUES (id, a1, a2, a3, a5, a6, a7, a8, a9, a10, a11, a13, a14, a22, a29, a17, a26, a30, a33, a35, a40, a48, a49, a51, a52, a58);
利用事物删除和更新、插入数据
begin transaction --开始事务
rollback transaction --回滚事务
commit transaction --提交事务
添加表的字段
ALTER TABLE 表名 ADD 字段名 VARCHAR(20) NULL
表中添加自增id
alter table lianxi add id int primary key IDENTITY(1,1) NOT NULL
SqlServer查询不为空的字段
select * from table where content is not null and datalength(field)<>0
