MsSql 游标修改字段两个表关联表向另个表插入记录-CSDN博客

-- 方法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]