In this lab, I am going to use sql script to: - Create a table: to create a table, you need to check the existence first. If it already exists, you have to drop the table, then, create it. If you don’t check the existence and the table is already there, the creating table statement will break.
- Add a column: again you will check the existence of the column first and than add it
- Insert data into the table: you will delete data first, then insert data into the table. Check error after each sql statement (each insertion statement), and put the whole thing in sql transaction.
- Update some data in a certain row: for updating table or deleting table, you need to back up the existing table first just in case you want it some time. Give it any name, and select the whole table into the back up one. Then you can update or delete.
Have fun! 1. create table:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ZTable] GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[ZTable] ( [RequestorEmail] [varchar] (100) NOT NULL , [OrderNumber] [int] NULL , [UserLast] [varchar] (50) NULL , [UserFirst] [varchar] (50) NULL , [CompletedDate] [datetime] NULL , [Created] [datetime] NULL CONSTRAINT [DF_ZTable_Created] DEFAULT (getdate()), CONSTRAINT [PK_ZTable] PRIMARY KEY CLUSTERED ( [RequestorEmail] ) ON [PRIMARY] ) ON [PRIMARY] END GO
2: Add column IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_name ='ZTable' AND column_name = 'PriorityPercentage') ALTER TABLE dbo.ZTable ADD PriorityPercentage DECIMAL (18,4) NULL 3: Insert data delete from ZTable begin begin transaction AddQuestions INSERT INTO dbo.ZTable Values ( 'Diane@hotmail.com' ,12345 ,'Diane' ,'Coon ' ,'12/7/2006 12:58:10 PM' ,getdate() ,3.5 ) IF @@ERROR <> 0 BEGIN PRINT 'ERROR: Records inserted in ZTable not successfully rolled back' ROLLBACK TRAN RETURN END INSERT INTO dbo.ZTable Values ( 'Charlie' ,23456 ,'Charlie' ,'Carter' ,'12/7/2006 12:58:10 PM' ,getdate() ,4.2 ) IF @@ERROR <> 0 BEGIN PRINT 'ERROR: Records inserted in ZTable not successfully rolled back' ROLLBACK TRAN RETURN END commit transaction end 4: Update data: BEGIN TRANSACTION -- =========================================================================== -- BACKUP ZTable -- =========================================================================== PRINT 'BACKUP ZTable TABLE' IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS WHERE NAME = 'Dif_ZTable') BEGIN IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'ZTable') BEGIN SELECT * INTO DBO.Dif_ZTable FROM DBO.ZTable IF (@@ERROR<>0) BEGIN PRINT 'ERROR BACKING UP ZTable' ROLLBACK TRANSACTION RETURN END END END -- =========================================================================== -- Update ZTable -- =========================================================================== PRINT 'UPDATE ZTable' UPDATE DBO.ZTable SET UserLast = 'WWW' ,UserFirst = 'ZZZ' WHERE OrderNumber = 12345
IF @@ERROR <> 0 BEGIN PRINT 'ERROR: ZTable failed. Terminating roll script' ROLLBACK TRAN RETURN END COMMIT TRANSACTION
|