TSQL Date Dimension Data Population Script

This post on DateDimension Data Population script. Back to TSQL Days...........

Happy Learning!!!

TSQL Tip for a Day

Today's post is learning from my question. Below is sample example

 

Step 1 - Create Table and Load Sample Data 

CREATE TABLE TestC

(Comments Char(100),)
INSERT INTO TestC VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G')SELECT * FROM TestC 

Step 2 - Pivot Query

DECLARE @Query VARCHAR(MAX)

DECLARE @cols VARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Comments) 
            FROM [dbo].[TestC]
            FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

 

SET @Query = '

SELECT * from ( SELECT *
    FROM TestC ) AS P 
PIVOT( MIN(Comments) FOR Comments in ('+ @cols + ') ) pvt'
 EXEC(@Query)    

 

Table Entries

 

Query Result

 

Couple of Interesting TSQL questions and answers from dba stackexchange site.

What is the difference between select count(*) and select count(any_non_null_column)?

• COUNT(*) will include NULLS
• COUNT(column_or_expression) won't.

Next Set of Questions List

• SQL query to convert columns into rows
• Generating large strings for test data
• How to select the first row of each group?
• SQL query that concatenate values from duplicate rows in a single table
• Select multiple rows from one row based on column values

Happy Learning!!!

TSQL - Temptables using Dynamic SQL

Interesting learning on Temptables using Dynamic SQL. When I use temp tables in Dymanic SQL my table creation failed. I have simplified this into example provided below.

--Example Code

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    DROP TABLE #T2;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO
-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO #T2 FROM T1 GROUP BY T1.a'
PRINT @sql 

EXEC sp_executesql @sql

SELECT * FROM #T2

When you execute this example you would receive error

--Error

Line 2: Msg 3701, Level 11, State 5:
Cannot drop the table '#T2', because it does not exist or you do NOT have permission.

There are two possible solutions for this. One is using global temptables (Use ## to make it global temp tables), second solution is create the table before calling insert records. I found similar examples while searching for this error.

Solution #1 - USE GLOBAL Temp Tables ##

--Example Code
IF OBJECT_ID('tempdb..##T2') IS NOT NULL
    DROP TABLE ##T2;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO
-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql nvarchar(max)
SET @sql = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO ##T2 FROM T1 GROUP BY T1.a'
PRINT @sql 
EXEC sp_executesql @sql
SELECT * FROM ##T2

Solution #2 - Based on post . CREATE the TABLE before calling the INSERT query


--Example Code
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    DROP TABLE #T2;
GO   
IF OBJECT_ID('tempdb..#T3') IS NOT NULL
    DROP TABLE #T3;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO

-- Test Tables

CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

CREATE TABLE #T2 (
a [int] NOT NULL,
b [int] NOT NULL)

CREATE TABLE #T3 (

a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data

INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql1 nvarchar(max)

SET @sql1 = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO #T3 FROM T1 GROUP BY T1.a'

PRINT @sql1

DECLARE @sql nvarchar(max)

SET @sql = 'INSERT INTO #T2(a,b) SELECT T1.a AS Col1, SUM(T1.b) AS Col2 FROM T1 GROUP BY T1.a'

PRINT @sql

EXEC sp_executesql @sql1

EXEC sp_executesql @sql

SELECT * FROM #T2

SELECT * FROM #T3

Table#3 had 0 records, Table #2 contained correct data set.

Happy Learning!!!

TSQL - Manager - Manager - Employee Recursive CTE Query

This post is based on my MSDN forum question. I have to implement this logic in one of procedures. Query Approach seems to be fine based on comments. Necessary foreign key is already in place. One of stackoverflow post was useful for me to arrive at solution.

Below is the solution: Input is Employee Id, Output is list of Employees working for the Manager.

CREATE TABLE Employee_Manager
( EmployeeId [int] NOT NULL,
  ManagerId [int] NULL)
  

INSERT INTO [dbo].[Employee_Manager] ([EmployeeId], [ManagerId])

VALUES (1,2),(3,1),(4,1),(5,4),(6,4)

SELECT * FROM [dbo].[Employee_Manager]

Manager - Employee

2       -   1
1       -   3
1       -   4
4       -   5
4       -   6

/* Return list of employee when managerid is passed as input */
DECLARE @MgrId [int]
SET @MgrId = 4
;WITH EmployeeHierarchy(ManagerID, EmployeeID)
AS
(
      SELECT [EM].[ManagerID], [EM].[EmployeeID]
      FROM [dbo].[Employee_Manager] EM
      WHERE [EM].[ManagerID] = @MgrId
      UNION ALL
      SELECT [EM].[EmployeeID], [EM].[EmployeeID]
      FROM [dbo].[Employee_Manager] EM
      INNER JOIN [EmployeeHierarchy] EH ON
      EM.[ManagerID] = [EH].[EmployeeID]
)   
SELECT [EMH].[EmployeeID], [EMH].[ManagerID]
FROM [EmployeeHierarchy] EMH

Please feel free to comment on this post for any better solution...

More Reads
T-SQL Tuesday #18 - CTEs - The permission hierarchy problem


Happy Learning!!!

TSQL - Pivot without Aggregating Results

This is based on today's work. I had two tables Table A and Table B. I had to Pivot results of Table B and update the results. There were two more constraints

• Table B can Have 0 to 10 Records for Each Id in Table A
• We were using Entity Framework and Calling the Proc
• For this I would need to provide the result set (Fixed Schema)

Below is the solution approach

• For dynamic pivot this post was helpful to get started
• Created a dummy record with ten results to achieve Fixed Schema Results. This record would be deleted before sending results
• I was not aware till date how EF handling Stored Procs. This post was very useful as my proc returned error. Changed Setting to SET FMTONLY OFF based on the post

Getting to Actual Example 

--Step 1

IF OBJECT_ID('dbo.TableB') IS NOT NULL
    DROP TABLE dbo.TableB;

--Step 2

IF OBJECT_ID('dbo.TableA') IS NOT NULL
    DROP TABLE dbo.TableA;

CREATE TABLE TableA (
    Id   INT          IDENTITY (1, 1) PRIMARY KEY,
    NAME VARCHAR (50) NULL
);

CREATE TABLE TableB (
    Id       INT           NOT NULL,
    Comments VARCHAR (500) NOT NULL,
    FOREIGN KEY (Id) REFERENCES TableA (Id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

IF OBJECT_ID('dbo.TableC') IS NOT NULL

    DROP TABLE dbo.TableC;

IF OBJECT_ID('tempdb..##TestResults') IS NOT NULL

    DROP TABLE ##TestResults;

CREATE TABLE TableC (

    Id         INT          NULL,
    Comments   VARCHAR (50) NULL,
    [RowCount] INT          NULL
);

--Step 3 
INSERT  INTO [dbo].[TableA] ([NAME])
VALUES                     ('A'),
('B'),
('C');

INSERT  INTO [dbo].[TableB] ([Id], [Comments])

VALUES                     (1, 'A Comments'),
(1, 'A Second Comments'),
(1, 'A 3rd Comments'),
(2, 'B 1st Comments'),
(2, 'B 2nd Comments'),
(2, 'B 3rd Comments');

--Step 4
WITH   CTE ([Id], [Comments], [RowCount])
AS     (SELECT [T].[Id],
               [T].[Comments],
               row_number() OVER (PARTITION BY [Id] ORDER BY [Id] ASC)
        FROM   TABLEB AS T)
INSERT INTO TableC (Id, Comments, [RowCount])
SELECT [CA].[Id],
       [CA].[Comments],
       [CA].[RowCount]
FROM   [CTE] AS CA;

--Step 5, Insert Dummy Records
INSERT  INTO [dbo].[TableC] ([Id], [Comments], [RowCount])
VALUES                     (999999, 'Test', 1),
(999999, 'Test', 2),
(999999, 'Test', 3),
(999999, 'Test', 4),
(999999, 'Test', 5),
(999999, 'Test', 6),
(999999, 'Test', 7),
(999999, 'Test', 8),
(999999, 'Test', 9),
(999999, 'Test', 10);

DECLARE @sql AS NVARCHAR (MAX);

SET @sql = N'SELECT [ID]';

SELECT   @sql = @sql + ',MAX(CASE WHEN [RowCount] =' + CAST ([RowCount] AS CHAR (5)) + ' THEN [Comments] ELSE '''' END) AS [Col' + CAST ([RowCount] AS CHAR (5)) + ']'

FROM     [dbo].[TableC]
GROUP BY [dbo].[TableC].[RowCount]
ORDER BY [dbo].[TableC].[RowCount];

SET @sql = @sql + N'

                    INTO ##TestResults FROM  [dbo].[TableC]
                    GROUP BY  [dbo].[TableC].[Id]
                    ORDER BY  [dbo].[TableC].[Id]';

EXECUTE sp_executesql @sql;

DELETE ##TestResults

WHERE  [Id] = 999999;

SELECT *

FROM ##TestResults;

Please feel free to comment this post for any better approach to handle this scenario.

More Reads

Dumping SQL data in pivoted format
SQL query to convert columns into rows

Happy Learning!!!

SQL Tip of the Day

SQL Learning for the Weekend. Today's tip is learning from article Ten Common Database Design Mistakes

Taking Lessons and advice from the article, What are my checklist while designing a Database Table

1. Normalized Table - For OLTP System verify Table design is Normalized - Related Post -
Database Development Model

2. Naming Conventions - Naming tables in a way that is easy to understand from customer perspective - Related Post - Database Object Naming Rules

3. Ensuring Data Integrity aspects (Check Constraints, NOT NULL Values, Default Values, Primary Key, Foreign Key)

4. Selecting Proper Datatypes, Allocating Space for the data - Related Post - TSQL Tip of the Day

5. Indexes on the Tables based on queries run against the table

6. Archiving Details / Table Partitioning for the table. Related Post - Table partitioning basics

Templates for database objects creation is available in book Pro SQL Server 2005 Database Design and Optimization.

Happy Learning!!!

TSQL Tip of the Day

Today we will look at use of INTERSECT and EXCEPT.

• EXCEPT - Data exists in one table and does not exist in another table. In SET operations it is represented as A-B.
• INTERSECT - Data exists in both the table. Intersection in SET Operations.

Let's try an example. Step 1 - Creating Test Tables

 --STEP 1

 IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
 CREATE TABLE TestTable1
 (
 Number INT IDENTITY(1,1) PRIMARY KEY,
 A VARCHAR(20) NOT NULL,
 B VARCHAR(20) NOT NULL,
 C VARCHAR(20) NOT NULL,
 D VARCHAR(20) NOT NULL
 )
 GO
IF OBJECT_ID ('TestTable2') IS NOT NULL DROP TABLE TestTable2
 CREATE TABLE TestTable2
 (
 Number INT IDENTITY(10,5) PRIMARY KEY,
 A VARCHAR(20) NOT NULL,
 B VARCHAR(20) NOT NULL,
 C VARCHAR(20) NOT NULL,
 D VARCHAR(20) NOT NULL
 )

Step 2 - Populate Test Data
--STEP 2
--Insert Records into this table
 DECLARE @I INT
 SET @I = 1
 WHILE 1 = 1
 BEGIN
 SET @I = @I + 1
 INSERT INTO TestTable1(A,B,C,D)
 VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
 (CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))

 INSERT INTO TestTable2(A,B,C,D)

 VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
 (CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))
  IF @I=1000
 BREAK;
 END

Step 3 - EXCEPT Example
--Number which is present in TestTable1 and not in TestTable2
SELECT Number
FROM dbo.TestTable1
EXCEPT
SELECT Number
FROM dbo.TestTable2

--Multiples of 5 excluded in results
--Alternate query

SELECT T1.Number
FROM dbo.TestTable1 T1
WHERE NOT EXISTS
(SELECT T2.Number
FROM dbo.TestTable2 T2 WHERE T2.Number = T1.Number)

Step 4 - INTERSECT Example 

--Number common in both TestTable1 and TestTable2
SELECT Number
FROM dbo.TestTable1
INTERSECT
SELECT Number
FROM dbo.TestTable2

--Alternate query
SELECT T1.Number
FROM dbo.TestTable1 T1
WHERE EXISTS
(SELECT T2.Number
FROM dbo.TestTable2 T2 WHERE T2.Number = T1.Number)

--Multiples of 5 listed in results

From MSDN - Reference

Happy Reading!!!