MsSql 游标 修改字段两个表关联 表向另个表插入记录详解数据库

-- 方法1：游标 
-- 声明变量 
DECLARE 
@SystemUserId AS UNIQUEIDENTIFIER 
 
-- 声明游标 
DECLARE C_SystemUser CURSOR FAST_FORWARD FOR 
SELECT SystemUserId  
FROM Quotation.dbo.SystemUser WHERE SystemUserNo NOT IN ('beijing','dicai','admin','test') ; 
 
OPEN C_SystemUser; 
 
-- 取第一条记录 
FETCH NEXT FROM C_SystemUser INTO @SystemUserId; 
 
WHILE @@FETCH_STATUS=0 
BEGIN 
-- 操作 
 
INSERT INTO Quotation.dbo.SystemUserRole(SystemUserId,SystemRoleId) VALUES(@SystemUserId,'549F845D-0F84-4422-9625-BFA2703288DD') 
-- 取下一条记录 
FETCH NEXT FROM C_SystemUser INTO @SystemUserId; 
END 
 
-- 关闭游标 
CLOSE C_SystemUser; 
 
-- 释放游标 
DEALLOCATE C_SystemUser; 
 
  
 
--修改字段两个表关联 
UPDATE a SET a.Password=b.Password 
FROM  a inner join  b on a.SystemUserNo=b.SystemUserNo  
where a.SystemUserNo!='admin' 
 
一个表向另一个表插入记录 
 
假如a表存在,则 
insert into A(a,b,c) select a,b,c from B 
 
假如A表不存在,则 
select a,b,c into A from B 
 
  
 
--新增表字段 
 
if not exists(select * from syscolumns where id=object_id('Provider') and name='Creater') 
begin 
alter table Provider add Creater uniqueidentifier null  
END 
 
--添加给字段 默认值 
 
ALTER TABLE [dbo].[QuotationFille] ADD CONSTRAINT [DF_QuotationFille_Status] DEFAULT ((0)) FOR [Status] 
 
ALTER TABLE [dbo].[QuotationFille] ADD CONSTRAINT [DF_QuotationFille_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn]