Sample Script for Database Modification Articles

In order to follow along and try the examples for my various data modification articles please run the following script to create a test table using SSMS (SQL Server Management Studio).

This script should be run using Microsoft SQL Server Management Studio on the Adventure Works database. You can get started using these free tools using my Guide Getting Started Using SQL Server

This script can be used to help with example from my various articles covering:

INSERT – Introduction to the INSERT statement.
UPDATE – Introduction to the UPDATE Statement.
DELETE – Introduction to the DELETE Statement.

MERGE – Introduction to the MERGE Statement.

When you’re finished, you can remove the table. I’ll show you how!

Script to Create Sample Table

The follow script can be used to create the table we’ll use:

USE [AdventureWorks2012]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[esqlSalesPerson](
                [SalesPersonID] [int] IDENTITY(1,1) NOT NULL,
                [FullName] [varchar](50) NOT NULL,
                [SalesLastYear] [money] NULL,
                [City] [nvarchar](50) NULL,
                [rowguid] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

Just open a new query windows in SSMS, past the above code in, and run it.

Script For MERGE Examples

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

--remove previously created tables so have a fresh copy
DROP TABLE esqlProductSource
DROP TABLE esqlProductTarget

-- create sample tables
CREATE TABLE [dbo].[esqlProductSource](
       [ProductID] [int] NOT NULL,
       [Name] [varchar](50) NULL,
       [ProductNumber] [varchar](50) NULL,
       [Color] [varchar](50) NULL,
 CONSTRAINT [PK_esqlProductSource] PRIMARY KEY CLUSTERED
(
       [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[esqlProductTarget](
       [ProductID] [int] NOT NULL,
       [Name] [varchar](50) NULL,
       [ProductNumber] [varchar](50) NULL,
       [Color] [varchar](50) NULL,
 CONSTRAINT [PK_esqlProductTarget] PRIMARY KEY CLUSTERED
(
       [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO esqlProductSource(ProductID, Name, ProductNumber, Color)
SELECT ProductID
      ,Name
      ,ProductNumber
      ,Color
  FROM Production.Product


INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)
SELECT ProductID
      ,Name
      ,ProductNumber
      ,Color
  FROM Production.Product

--- make changes to source:
Update esqlProductSource
SET Color = 'Unknown'
WHERE Color is NULL

DELETE
FROM esqlPRoductSource
WHERE Name LIKE '%Tape%'

INSERT INTO esqlProductSource(ProductID, Name, ProductNumber, Color)
Values (999100, 'Crank Shaft','CS-1010','Sliver'),
       (999110, 'Super Sprocket', 'SS-1010', 'Gray')

Script to Remove Tables.

Once you are done with the table, you can run the following script to remove it from your database:

USE [AdventureWorks2012]
DROP TABLE esqlSalesPerson
DROP TABLE esqlProductSource
DROP TABLE esqlProductTarget