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