Summary of standardized SQL data modification statements

1. Add a field Copy codeThe Code is as follows: if not exists (SELECT * from syscolumns where id = OBJECT_ID ('querydatasource ') and name = 'iscrosstable ')
BEGIN
Alter table QueryDataSource ADD IsCrossTable bit default (0) -- insert Field
END

2 stored proceduresCopy codeThe Code is as follows: if exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [PLSystem_Insert] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [PLSystem_Insert]
Go
Create procedure dbo. PLSystem_Insert
(
@ PLSystemID smallint,
@ PLSystemName varchar (20 ),
@ PLSystemFlag char (2)
)
AS
Insert into dbo. PLSystem
(
PLSystemID,
PLSystemName,
PLSystemFlag
)
VALUES
(
@ PLSystemID,
@ PLSystemName,
@ PLSystemFlag
)
Go

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [PLSystem_Delete] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [PLSystem_Delete]
Go
Create procedure dbo. PLSystem_Delete
(
@ PLSystemID smallint
)
AS
Delete from dbo. PLSystem
WHERE
PLSystemID = @ PLSystemID
Go

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [PLSystem_Update] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [PLSystem_Update]
Go
Create procedure dbo. PLSystem_Update
(
@ PLSystemID smallint,
@ PLSystemName varchar (20 ),
@ PLSystemFlag char (2)
)
AS
UPDATE dbo. PLSystem SET
PLSystemName = @ PLSystemName,
PLSystemFlag = @ PLSystemFlag
WHERE
PLSystemID = @ PLSystemID
Go

3. Create a tableCopy codeThe Code is as follows: if exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [PLSystem] ') AND type in (n'u '))
Drop table [dbo]. [PLSystem]
GO
If not exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n' [dbo]. [PLSystem] ') AND type in (n'u '))
BEGIN
Create table [dbo]. [PLSystem] (
[PLSystemID] [tinyint] not null,
[PLSystemName] [varchar] (20) NULL,
[PLSystemFlag] [char] (2) NULL,
CONSTRAINT [XPKPLSystem] PRIMARY KEY CLUSTERED
(
[PLSystemID] ASC
)
) ON [PRIMARY]
END
GO

4 cursorCopy codeThe Code is as follows: Begin
Declare @ PluserID varchar (8)
Declare pluserCurse Cursor for select PLUserID from PLUser where PCancelJudge = 0
Open pluserCurse -- open the cursor
Fetch next from pluserCurse into @ PluserID
While @ FETCH_STATUS = 0
Begin
Print (@ PluserID)
Insert into PLUserActorDepart (DepartID, PLActorSystemID, PLUserID)
Select required mentid, PLSystemID, PLUserID from MSUserRight where MSUserRight. Required mentid not in
(
Select distinct mentid from PLDepartment where PLDepartment. DParent = (select distinct mentid from PLUser where PLUserID = @ PluserID)
OR PLDepartment. Repeated mentid = (select distinct mentid from PLUser where PLUserID = @ PluserID)
) AND MSUserRight. PLUserID = @ PluserID
Fetch next from pluserCurse into @ PluserID
End
Close pluserCurse
Deallocate pluserCurse
End

5. Use while to implement a for LoopCopy codeThe Code is as follows: Create Proc InsertSQL
AS
Begin
DECLARE @ Count int
Set @ Count = 0;
While (@ Count <200000)
Begin
Print (@ Count );
Insert into Student values ('Young ', 'M', 100, 'fujianxiament ');
Set @ Count = @ Count + 1;
END
END