In this lab, I am going to use sql script to:

1. 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.
2. Add a column: again you will check the existence of the column first and than add it
3. 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.
4. 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

      (

            '[email protected]'
            ,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