Okay here is the first table I tried to follow the codeing guidlines. Since I had created the tables as examples I had to do them by hand. 😛
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#UserMain','U') IS NOT NULL
DROP TABLE #UserMain
--===== Create the test table with CREATE TABLE #UserMain
(
UserNumber INT IDENTITY(1,1) Primary Key, --Is an IDENTITY column on real table,
UserName CHAR(30),
User_Status CHAR(1),
Base_State CHAR(2),
Years INT )
--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #UserMain ON
--===== Insert the test data into the test table
INSERT INTO #UserMain
(UserNumber, UserName, User_Status, Base_Status, Years)
SELECT '1','Bill','C','VA','2' UNION ALL
SELECT '2','Lisa','C','MA','5' UNION ALL
SELECT '3','Robert','N','NJ','1' UNION ALL
SELECT '4','Cindy','D','WV','5' UNION ALL
SELECT '5','John','C','NH','10' UNION ALL
SELECT '6','Lincoln','C','NJ','3' UNION ALL
SELECT '7','Jake','C','NJ','4'
Okay here is the second table.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Professional','U') IS NOT NULL
DROP TABLE #Professional
--===== Create the test table with CREATE TABLE #Professional
(
Lic_Number INT IDENTITY(1,1) Primary Key, --Is an IDENTITY column on real table,
UserNumber INT,
Type CHAR(2),
SubCat CHAR(2),
)
--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Professional ON
--===== Insert the test data into the test table
INSERT INTO #Professional
(Lic_Number, UserNumber, Type, SubCat)
SELECT '1251','1','JP','SJ' UNION ALL
SELECT '1584','1','PP','SP' UNION ALL
SELECT '2541','2','JP','SJ' UNION ALL
SELECT '4558','5','PP','SP' UNION ALL
SELECT '0125','4','JP','SJ' UNION ALL
SELECT '1558','3','JP','SJ' UNION ALL
SELECT '5841','5','PP','SP' UNION ALL
SELECT '1105','6','JP','SJ' UNION ALL
SELECT '6765','7','JP','SJ' UNION ALL
SELECT '1587','6','PP','SP'