Category: T-SQL

This SQL allows appending to an existing table with the data from an another table.

INSERT INTO dbo.mytableTEST01
SELECT * FROM mytable

I sometimes need to just simply copy a small table to compare it against another run of the same program or some other legitimate reason to copy a table.

This SQL allows creating a new table and populating it with the data from an existing table.

SELECT *
INTO dbo.mytableTEST01
FROM (select * from mytable) G1

This SQL is helpful when searching for specific strings within SQL code. I have used this code to search in databases too many times to mention. It is especially helpful when looking at a legacy database that I have never looked at before when trying to find things.

SELECT *
FROM sysobjects
WHERE id IN (
		SELECT id
		FROM syscomments
		WHERE TEXT LIKE '%Outstanding orders confirmed %' 
		)
ORDER BY NAME

I’ve always used the Microsoft SQL Management Studio Execution plan feature to visualize the query plan. However today I was experimenting with the SQL XML showplan statement to see if I could learn anything more about execution plans by looking more closely at the code. As soon as I saw the XML it occurred to me that I could use XSLT to convert the output to HTML similar to the way that the SQL Management studio did it. I was curious to try, but before I writing my own, I checked to see if anyone else did it already and found this on github:

• JustinPealing/html-query-plan

So, to demonstrate I created a simple SQL using temp tables so that I could perform some very basic tests.

IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header
IF OBJECT_ID('tempdb..#detail') IS NOT NULL DROP TABLE #detail

set nocount on

create table #header
(
 order_no int,
 name  varchar(30),
 email varchar(255),
)

 create table #detail
(
 order_no int,
 item  varchar(30),
 item_code  varchar(30)
 )

Insert some sample data

 insert into #header (order_no,name,email) values (121,'Joe Blogs','j.bloggs@bar.com')
 insert into #header (order_no,name,email) values (122,'Bill Smith','B.Smith@foo.com')

 insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-001')
 insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-002')
 insert into #detail (order_no,item,item_code) values (121,'Item 1','EV-003')
 
 insert into #detail (order_no,item,item_code) values (122,'Item 1','IT-001')
 insert into #detail (order_no,item,item_code) values (122,'Item 2','IT-002')
 insert into #detail (order_no,item,item_code) values (122,'Item 3','IT-003')

Turn on the SHOWPLAN_XML in SQL and run query to produce the XML.

SET SHOWPLAN_XML ON;
go
 SELECT
      h.order_no
    , h.name
    , h.email
    , d.item
    , d.item_code
FROM #header h
JOIN #detail AS d ON h.order_no = d.order_no
WHERE NOT EXISTS (
      SELECT NULL
      FROM #detail od
      WHERE od.item_code = 'EV-003'
      AND h.order_no = od.order_no
      )
GO
SET SHOWPLAN_XML Off;
GO

Then copy and paste the output XML data into a file and upload to the demo page that uses Justins HTML query plan visualizer library.

HTML XML Upload demo