x002548's Blog

SET BASED Number Table Create Hack

Brett Kaiser (x002548) — Fri, 29 Oct 2010 16:29:32 GMT

Well...don't think I've seen this hack before..I guess you might call it a setbased way to create a numbers table. Who has done it this way before? USE [dba] GO CREATE TABLE n(n int) GO INSERT INTO n(n) SELECT CONVERT(int,m.n + hth.n + tth.n + th.n + h.n + t.n + u.n) AS [myDay99] FROM (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS u CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS t CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS h CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS th CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS tth CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS hth CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS m ORDER BY 1 GO

XML, SQL & DB2 V10

Brett Kaiser (x002548) — Fri, 08 Oct 2010 19:41:30 GMT

...or I guess it could be any platform.. Webcast/Conf Call on Monday...IBM Rep after 3 hours...I piped in... X002548: "ummm, excuse me, so are you saying that XML database models outperforms the relational model?" IBM: "In what regard?" X002548: "Data access and modifications?" IBM: "......crickets.....ummm no, the relational model is faster..."

Next Available Date

Brett Kaiser (x002548) — Wed, 30 Jun 2010 18:43:49 GMT

Saw a post the other day on SQLTeam.

The OP Asked: "If I supply a Date, How can I get the next available date, that is not a holiday, and is not on the weekend."

I came up with this hack...please let me know if anyone has come with a different version

Thanks

Brett

CREATE TABLE Holidays (Holiday_Dt datetime, HoldayName varchar(255))
GO

INSERT INTO Holidays(Holiday_Dt, HoldayName)
SELECT '1/1/2010', 'New Years Day' UNION ALL
SELECT '1/18/2010', 'Martin Luther King' UNION ALL
SELECT '2/15/2010', 'President''s Day' UNION ALL
SELECT '5/31/2010', 'Memorial Day' UNION ALL
SELECT '7/5/2010', 'July 4th Holiday' UNION ALL
SELECT '9/6/2010', 'Labor Day' UNION ALL
SELECT '11/25/2010', 'Thanksgiving' UNION ALL
SELECT '11/26/2010', 'Black Friday' UNION ALL
SELECT '12/24/2010', 'Christmas Holiday' UNION ALL
SELECT '12/31/2010', 'New Years Eve'
GO

DECLARE @inp_Dt datetime; SET @inp_Dt = '11/25/2010'

    SELECT TOP 1 CASE WHEN Holiday_Dt IS NULL THEN inp_Dt ELSE DATEADD(d,n,inp_Dt) END AS Next_Available_Dt
      FROM (SELECT @inp_Dt AS inp_Dt) AS XXX
LEFT JOIN Holidays h
  ON h.Holiday_Dt = xxx.inp_Dt
LEFT JOIN (SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION
   SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n) AS n
  ON xxx.inp_Dt < DATEADD(d,n,inp_Dt)
     WHERE DATENAME(dw,DATEADD(d,n,inp_Dt)) NOT IN ('Saturday','Sunday')
    AND NOT EXISTS (SELECT * FROM Holidays WHERE Holiday_Dt = DATEADD(d,n,inp_Dt))
GO

DROP TABLE Holidays
GO

DB2 OS/390 Database COPY and RESTORE

Brett Kaiser (x002548) — Thu, 03 Dec 2009 18:32:17 GMT

Hey there!!!!!

In the SQL Server world, if you want to create a database on a same server instance, from an existing database, all you have to do is something like:

RESTORE DATABASE <db_name>
   FROM DISK = '\\<file path>\<file name>'
   WITH   MOVE '<logical data file name>_data' TO '\\<file path>\<file name>.MDF'
        , MOVE '<logical log file name>_log' TO '\\<file path>\<file name>.LDF'
    , REPLACE
GO

In DB2? OH NO!!!   SORRY!!!

I guess you could use Platinum or some other tool, but that's like a mainframe wizard, and they always (wizards in general) are never 100% bullet proof.

At least not for my liking.

Anyway....first thing you gotta do is to make sure that your destination database is completely created with th DDL from the source Database

From here, there are a couple of ways to go.

One requires the involvement of the DB2 Management system less than the other.

The latter (which is easier, straight forward, easier to understand, and takes WAY longer) , requires you to unload all of the tables, check/convert DB2 Load cards, put all of the tables spaces in utility pending, set up all of the load JCL for each, then making sure the dfata is then loaded in the correct RI order, submit the JCL, Run Statics, and take the tables out of Copy Pending.

The Other requires you to Create Image Copies (This is like a SQL Server Dump, but imagine that each table is a file).

From that you will use a DSN1COPY to put everything back.

The DB2MS Doesn't know about any of the contsraints, because it's basically "under the covers".

The only Complication, is that you have to tell The Copy the actual Object ID's of each object, mapped from the source, to the target...which is a royal pain....

UNTIL NOW!

All of the Id's are in the System Catalogs...if Both of the DB's are on the same subsystem (and assuming you've built the target DB with all of the DDL from the First), you can run the following SQL and it will create the mapping for you.

You can the take the output and type in all the mappings, or like I would do, is take the results, and generate everything I need.

I will post more about the IMAGECOPY, DSN1COPY, and the Code to create the mapping for the copy in future posts.

Anyway, I hope I gave a glimpse into 2 different worlds, and some help for us poor z/OS 390 DB2 Souls.

And HEY, if anyone has a better solution, I AM NOT PROUD. Please post it.

Thanks

SQL QUERY          BXHRSPDB.COMPARE_TABLES                     LINE    1


    SELECT CREATOR1
         , CREATOR2
         , DBNAME1
         , DBID1
         , DBNAME2
         , DBID2
         , TSNAME1
         , TSID1
         , TSNAME2
         , TSID2
         , TBNAME1
         , TBID1
         , TBNAME2
         , TBID2
      FROM (
    SELECT SUBSTR(TS2.CREATOR,1,8) AS CREATOR2
         , SUBSTR(TS2.DBNAME,1,8) AS DBNAME2
         , TS2.DBID                AS DBID2
         , SUBSTR(TS2.NAME,1,8)    AS TSNAME2
-- DSN1COPY WANT PSID (PAGE SET ID), NOT OBID (FILE ID)
         , TS2.PSID                AS TSID2
         , SUBSTR(TB2.NAME,1,18)   AS TBNAME2
         , TB2.OBID                AS TBID2
      FROM SYSIBM.SYSTABLESPACE TS2
LEFT JOIN SYSIBM.SYSTABLES     TB2
        ON TS2.DBNAME    = TB2.DBNAME
       AND TS2.NAME      = TB2.TSNAME
     WHERE TS2.CREATOR   = 'BXHRSPDC'
       AND TS2.DBNAME    = 'SBD000DC'
       AND TB2.TYPE = 'T'
) AS C1
FULL JOIN (
    SELECT SUBSTR(TS1.CREATOR,1,8) AS CREATOR1
         , SUBSTR(TS1.DBNAME,1,8) AS DBNAME1
         , TS1.DBID                AS DBID1
         , SUBSTR(TS1.NAME,1,8)    AS TSNAME1
-- DSN1COPY WANT PSID (PAGE SET ID), NOT OBID (FILE ID)
         , TS1.PSID                AS TSID1
         , SUBSTR(TB1.NAME,1,18)   AS TBNAME1
         , TB1.OBID                AS TBID1
      FROM SYSIBM.SYSTABLESPACE TS1
LEFT JOIN SYSIBM.SYSTABLES     TB1
        ON TS1.DBNAME    = TB1.DBNAME
       AND TS1.NAME      = TB1.TSNAME
     WHERE TS1.CREATOR   = 'BXHRSPDB'
       AND TS1.DBNAME    = 'SBD000DB'
       AND TB1.TYPE = 'T'
) AS C2
--     ON DBNAME1=DBNAME2
       ON TSNAME1=TSNAME2
      AND TBNAME1=TBNAME2
;

Parsing String in SQL Server Hack

Brett Kaiser (x002548) — Fri, 20 Nov 2009 17:45:40 GMT

Alrighty Then

How to Parse a string in SQl...not always easy...I imagine it really shouldn't have been difficult for M$ to make some T-SQL extensions, but they didn't (or at least up to 2k5...gotta look more into 2k8)

Even in REXX we had some nice features...like MASK, WORD, WORDS, etc.

So SQL Server Database devloper slaves are usually left with a myriad of slick hacks..REVERSE, CHARINDEX (to the nth degree), and ultimatley looping with logic in a User Defined Function (UDF)

In any case, we had an OP in this thread, "Parse String, Extract Multiple Parts of a Sting" ask how to parse out a string...so, I started my Daily "Practice"...then was tired of the same old thing.

Now, I don't know if anyone has done this before (I haven't seen it), but this access a table once, does no looping, eliminates the indecipherable n levels of CHARINDEX, and employees a series of nested derived tables.

Hope you like the hack

CREATE TABLE #myTable99(Col1 varchar(50))
GO

INSERT INTO #myTable99(Col1)
--                 1         2         3         4         5         6
--        123456789012345678901234567890123456789012345678901234567890
SELECT 'LDAP://adag.agaga,OU=audit,OU=NYC,OU=Paris,ad.fafsadfs,hjhkj' UNION ALL
SELECT 'LDAP://adagagaga,OU=finance,OU=LA,OU=London,adfafsadfs' UNION ALL
SELECT 'LDAP://adagagaga,OU=Marketing,OU=Dublin,adfaf.sadfs'
GO

SELECT    CASE
            WHEN Second_OU = 0 THEN NULL ELSE
            SUBSTRING(Col1, (First_OU+3) ,(First_Comma-1) -(First_OU+3)+1)
            END AS FirstValue
        , First_OU
        , First_Comma
        , CASE
            WHEN Second_OU = 0 THEN NULL ELSE
            SUBSTRING(Col1, (Second_OU+3),(Second_Comma-1)-(Second_OU+3)+1)
            END AS SecondValue
        , Second_OU
        , Second_Comma
        , CASE
            WHEN Third_OU = 0 THEN NULL ELSE
            SUBSTRING(Col1, (Third_OU+3) ,(Third_Comma-1) -(Third_OU+3)+1)
            END AS ThirdValue
        , Third_OU
        , Third_Comma
FROM (
    SELECT Col1
        , First_OU
        , First_Comma
        , Second_OU
        , Second_Comma
        , CHARINDEX('OU=',Col1, Second_Comma) AS Third_OU
        , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1, Second_Comma)) AS Third_Comma
    FROM (
        SELECT Col1
            , First_OU
            , First_Comma
            , CHARINDEX('OU=',Col1, First_Comma) AS Second_OU
            , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1, First_Comma)) AS Second_Comma
        FROM (SELECT Col1
                    , CHARINDEX('OU=',Col1) AS First_OU
                    , CHARINDEX(',',Col1,CHARINDEX('OU=',Col1)) AS First_Comma
                FROM #myTable99
                ) AS XXX
            ) AS YYY
    ) AS ZZZ
GO

DROP TABLE #myTable99
GO

Replace a Character Anywhere in a DB

Brett Kaiser (x002548) — Mon, 09 Nov 2009 20:03:39 GMT

Ran across this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135575

And figured I'd post this. The code below will generate update statement for all tables that have a char data type. It will then replace a quote (") with an empty sting

Guess you could automate this, but this is simple enough. Just cut and paste the results and execute that. I guess you could go on and schedule it to run nightly to do a clean up.

Hope you have a backup BEFORE you run the result set

CREATE TABLE #myTemp99 (n int IDENTITY(1,1), TABLE_NAME varchar(256), COLUMN_NAME varchar(256))
GO

INSERT INTO #myTemp99(TABLE_NAME, COLUMN_NAME)
    SELECT TABLE_NAME, COLUMN_NAME
       FROM INFORMATION_SCHEMA.Columns
    WHERE DATA_TYPE IN ('char','varchar', 'nchar','nvarchar')
   ORDER BY TABLE_NAME, COLUMN_NAME
GO

SELECT * FROM #myTemp99
GO

SELECT SQL FROM (
SELECT 'UPDATE ' + TABLE_NAME + CHAR(13) + CHAR(10)
        + '   SET ' + COLUMN_NAME + ' = REPLACE('
        +COLUMN_NAME+','+''''+'"'+''''+','+''''+''''+')' AS SQL
, TABLE_NAME, 1 AS SQLGroup, n
FROM #myTemp99 o
WHERE EXISTS (SELECT TABLE_NAME
                 FROM #myTemp99 i
                WHERE i.TABLE_NAME = o.TABLE_NAME
            GROUP BY i.TABLE_NAME
               HAVING MIN(i.n) = o.n)
UNION ALL
SELECT '     , ' + COLUMN_NAME + ' = REPLACE('
        +COLUMN_NAME+','+''''+'"'+''''+','+''''+''''+')' AS SQL
, TABLE_NAME, 2 AS SQLGroup, n
FROM #myTemp99 o
WHERE EXISTS (SELECT TABLE_NAME
                 FROM #myTemp99 i
                WHERE i.TABLE_NAME = o.TABLE_NAME
            GROUP BY i.TABLE_NAME
               HAVING MIN(i.n) <> o.n)
UNION ALL
SELECT 'GO' AS SQL
, TABLE_NAME, 3 AS SQLGroup, 9999999 AS n
FROM #myTemp99 o
WHERE EXISTS (SELECT TABLE_NAME
                 FROM #myTemp99 i
                WHERE i.TABLE_NAME = o.TABLE_NAME
            GROUP BY i.TABLE_NAME
               HAVING MIN(i.n) = o.n)
) AS XXX
ORDER BY TABLE_NAME, SQLGroup, n

DROP TABLE #myTemp99
GO

How can I update Multiple Tables at once

Brett Kaiser (x002548) — Thu, 03 Apr 2008 13:49:05 GMT

"CAN'T BE DONE" -- Crazy boy

Well at first glance, that may very well be the answer, as in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100207

Now, this might not be what the OP is looking for, but if you employ a partitioned view, then yes, it's doable

cut and paste the sample code to see it in action

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 1 AND 10), Col2 varchar(50))
CREATE TABLE myTable98 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 11 AND 20), Col2 varchar(50))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'x' UNION ALL
SELECT 2, 'y' UNION ALL
SELECT 3, 'z'

INSERT INTO myTable98(Col1, Col2)
SELECT 11, 'x' UNION ALL
SELECT 12, 'y' UNION ALL
SELECT 13, 'z'
GO

CREATE VIEW myView99
AS
SELECT Col1, Col2 FROM myTable99
UNION ALL
SELECT Col1, Col2 FROM myTable98
GO

SELECT * FROM myView99

UPDATE myView99 SET Col2 = 'x002548' WHERE Col2 = 'z'

SELECT * FROM myView99

/*
DROP VIEW myView99
DROP TABLE myTable99, myTable98
*/

Because you're mine, I walk the line

Brett Kaiser (x002548) — Thu, 26 Jul 2007 19:45:36 GMT

....ummm, well, that "line" would be walking through a line in a family tree. I saw a post the other the day that asked, if I know a relative somewhere in a families "lineage", how can I find the entire family tree from top to bottom.

Well here's a hack to do that...don't ask for effecienciey. This for SQL Server 2000. I have to determine if CTE's in 2k5 can come up with a better solution, but here it is for now.

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie',     '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob',       '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy',     '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don',       '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily',     '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank',     '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry',     '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1    UNION ALL
SELECT 3, 2    UNION ALL
SELECT 4, 3    UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5    UNION ALL
SELECT 7, 6    UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
BEGIN
SELECT @p_save = @p
SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
BEGIN
SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
END
RETURN @rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @p int, @p_save int, @rs varchar(8000)
SELECT @p = 0, @p_save = 0
SELECT @p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @Child
--Loop Until @@rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @p)
BEGIN
SELECT @p_save = @p
SELECT @p = ParentID_FK FROM Child c WHERE ID_PK = @p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @@rowcount = 0
SELECT @p = @p_save
SELECT @rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @p)
BEGIN
SELECT @rs = @rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @p
SELECT @p = ID_PK FROM Child WHERE ParentID_FK = @p
END
SELECT @rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO

Alias to be or not to be

Brett Kaiser (x002548) — Tue, 27 Mar 2007 18:59:43 GMT

EDIT: This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around.

We were having a discussion over at SQLTeam on whether to use full table name aliases or short aliases to label columns. It always seemed to be a mattter of preference and debate on how self docuenting the code. For m I will always use short aliases and make sure I lable every column, even if it's unique, just so when I come back to the code I don't have to guess or go to the data model. Some suggest that that's not good enough and fully qualify the columns with the name. I think that is overkill, but hey, to each his or her own.

Then we got into a discussion about performance and if there was any. My first thought was, no way...then someone asked if anyone had ever tested it...so I got to thinking...ys, yes, I know, a dangerous proposition. And while this is not diffinitive by any means, and I've seen varying results, but the table without the aliases at all took longer. I have to run some more complicated tests, but this is what we get from the following code.

ShortLabelTime
--------------
93

FullLabelTime
-------------
93

NoLableTime
-----------
126

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()))
GO

DECLARE @x int
SELECT @x = 1
WHILE @x < 10000
BEGIN
INSERT INTO myTable99(Col2) SELECT 'x'
SELECT @x = @x + 1
END
GO

DECLARE @s datetime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT a.Col1, a.Col2, a.Col3 FROM myTable99 a WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS ShortLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT myTable99.Col1, myTable99.Col2, myTable99.Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS FullLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT Col1, Col2, Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS NoLableTime
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Add Foreign Keys Back to the Database

Brett Kaiser (x002548) — Thu, 01 Mar 2007 14:49:24 GMT

"OK Brett, Now that I Removed all my Foreign Keys to Truncate the Data, Now What? I'm Hosed! Thanks a bunch"

OK, Well....sorry to keep you hangng out there. But, if you followed the code in the above link you will have all of the RI saved to the work table, so now all you need to do is replay it. The following is the code that will do this for you. Again, sorry for the delay.

CREATE procedure isp_exec_FK_code

AS

DECLARE @FKcode nvarchar(3000)
DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int
SET NOCOUNT ON
SELECT @rc = 0
DECLARE FKcode cursor fast_forward read_only for

SELECT * FROM FK_Create_code

OPEN FKcode

FETCH NEXT FROM FKcode
INTO @FKcode

WHILE @@fetch_status = 0
BEGIN
execute sp_executesql @FKcode
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_exec_FK_code_Error
  END
FETCH NEXT FROM FKcode
INTO @FKcode
END

isp_exec_FK_code_Exit:
CLOSE FKcode
DEALLOCATE FKcode
SET NOCOUNT OFF
RETURN @rc

isp_exec_FK_code_Error:

If @Error_Type = 50001
BEGIN
  Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
          + ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
          + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
          + ',"' + ' Message: ' + ',"' + RTrim(description)
          From master..sysmessages
        Where error = @error_out)
END

RAISERROR @Error_Type @Error_Message

GOTO isp_exec_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Collecting Requirements For Key Information

Brett Kaiser (x002548) — Thu, 22 Feb 2007 18:05:05 GMT

I've been asked to assist (this time BEFORE Project initiation for a change) in the developmennt of a new application. The Business Liason/Tech group that is doing this has been collecting requirements (basically reviewing an EXCEL Spreadsheet on steriods) and is coming up with a data model. We will be doing a model review, but I was able to guide them in how to record the information so I can leverage the data to generate the tables. I've done this several times already, and they are still fine tuning the table defintion. I then take that document (Excel, again) and generate the DDL and drop it into ERWin (I'll post that code later).

Now they are curious on how to define relationships, Primary Keys and Alternate keys. I decided to figure out the best way to guide them in documenting this so I could leverage that as well. So I came up with the following. Just ask your Business Liason group to record the information in the following form, then just use the code below and generate all of your code.

Basically, you need a spreadsheet with the follwoing information

Parent Table, Key Column, Child Table, Key Type, Key Order and Key Sequence.

Parent Table as it implies is the Parent in a relationship. For Alterante Keys, Primary Keys it is the table that key info is being generated for. Key Column is the Column that will be used in the key. Child Table as is implied the child of a relationship. It is only used for a Foreign Key type. Key Type is P for Primary, F for Foreign Key amd A for an Alternate Key. Key Order defines the Order of Columns in a key, and finally Key Sequence defines the Order in which Foreign or Alternate keys are created. It is also used as part of the index or constraint.

I have been on the wrong end of documentation gone bad too many times, and there is immense push back when you tell them you can't use it...OK, you can use, you have to read it, then retype everything that they already typed.

I hope you find this useful.

CREATE TABLE myTable99 (
Parent sysname
, keyColumn sysname
, Child sysname
, keyType char(1)
, keyOrder int
, keySequence int)
GO

INSERT INTO myTable99(Parent, keyColumn, Child, keyType, keyOrder, keySequence)
SELECT 'myEmployee', 'EMPL_ID', 'myDirectory', 'F', 1, 1 UNION ALL
SELECT 'myEmployee', 'EMPL_ID', ''           , 'P', 1, 1 UNION ALL
SELECT 'myEmployee', 'SSN'    , ''           , 'A', 1, 1 UNION ALL
SELECT 'myDirectory', 'PHONE' , ''           , 'P', 1, 1 UNION ALL
SELECT 'myDirectory', 'EMPL_ID', ''           , 'P', 2, 1
GO

SELECT * FROM myTable99
GO

CREATE TABLE myEmployee(EMPL_ID char(12) NOT NULL, SSN char(9), EMP_NAME varchar(50))
CREATE TABLE myDirectory(PHONE char(10) NOT NULL, EMPL_ID char(12) NOT NULL)
GO

SELECT SQL FROM (
SELECT DISTINCT 'ALTER TABLE ' + Parent
+ ' WITH NOCHECK ADD CONSTRAINT '
+ Parent + '_PK PRIMARY KEY (' AS SQL
, Parent, 1 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'P'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 2 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'P'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 3 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'P'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 4 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'P'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
, Parent, 5 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'P') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

/* Produces

ALTER TABLE myDirectory WITH NOCHECK ADD CONSTRAINT myDirectory_PK PRIMARY KEY (
PHONE
, EMPL_ID
)
GO
ALTER TABLE myEmployee WITH NOCHECK ADD CONSTRAINT myEmployee_PK PRIMARY KEY (
EMPL_ID
)
GO

SELECT SQL FROM (
SELECT DISTINCT 'CREATE UNIQUE INDEX '
+ Parent + '_AK'+CONVERT(varchar(3),keySequence)+' ON ' + Parent + ' ( ' AS SQL
, Parent, 1 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'A'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 2 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'A'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 3 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'A'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 4 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'A'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
, Parent, 5 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'A') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

/* Produces

CREATE UNIQUE INDEX myEmployee_AK1 ON myEmployee (
SSN
)
GO

SELECT SQL FROM (
SELECT DISTINCT 'ALTER TABLE ' + Child + ' ADD FOREIGN KEY (' AS SQL
, Parent, 1 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 2 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'F'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 3 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'F'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 4 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT DISTINCT 'REFERENCES ' + Parent + ' (' AS SQL
, Parent, 5 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT '   '+keyColumn AS SQL
, Parent, 6 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder = 1
   AND keyType = 'F'
UNION ALL
SELECT ' , '+keyColumn AS SQL
, Parent, 7 AS SQL_GROUP, keyOrder
FROM myTable99
WHERE keyOrder <> 1
   AND keyType = 'F'
UNION ALL
SELECT DISTINCT ' )' AS SQL
, Parent, 8 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F'
UNION ALL
SELECT DISTINCT 'GO' AS SQL
, Parent, 9 AS SQL_GROUP, 1 AS keyOrder
FROM myTable99
WHERE keyType = 'F') AS XXX
ORDER BY Parent, SQL_GROUP, keyOrder

ALTER TABLE myDirectory ADD FOREIGN KEY (
EMPL_ID
)
REFERENCES myEmployee (
EMPL_ID
)
GO

DROP TABLE myTable99
DROP TABLE myEmployee, myDirectory

What'dya mean I can't TRUNCATE Tables that have RI?

Brett Kaiser (x002548) — Tue, 20 Feb 2007 16:58:20 GMT

So, we've gotten into the business of sanitizing or scrambling sensitive production data for development environments. This is, for the most part, was the direction to do this for mainframe flat files. Now comes along distributed environments, mostly 3rd party vendor applications on sql server. You should see some of the twisted things these apps do. Using reserved words as column names, creating tables with the same name but different owners..the list is long.

In any case, when we told the people who support this mess that we would need fixed width flat files, they were.."but...but...we have over 1,000 tables". Long story short, I wrote a bunch of sprocs to automate alot of the steps needed to get the data out and put it back so they could have a sanitized environment.

One of the issues we ran across was how to handle all of this with RI. Now I guess I could have had them set up a data dictionary that show the relationships, but I choose a lazy way out. I used the catalogs to copy all the RI to a Work table, DROP All of the RI (This is done with another sproc I have yet o post) so that it could be "replayed" after I was done with the TRUNCATES and the bcp in's. Here's a sproc that will log all the RI for any database. Be forewarned...it takes dynamic sql to a whole other level. If anyone has anything simpler, I'd like to see it, but this works fine.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Gen_FK_code]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Gen_FK_code]
GO

CREATE procedure isp_Gen_FK_code
@dbname varchar(255)

AS
/*

EXEC isp_Gen_FK_code
@dbname = 'OHM_Prod'

SELECT * FROM FK_create_code

SET NOCOUNT ON
DECLARE @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
DECLARE @fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
DECLARE @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000)
DECLARE @DYSQL nvarchar(4000),@ColList sysname, @ColList2 sysname

DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int

SELECT @rc = 0
CREATE TABLE ##Key_Column_usage(Constraint_catalog sysname,CONSTRAINT_SCHEMA sysname, CONSTRAINT_NAME varchar(300)
,TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname
,ORDINAL_POSITION int)

SET @DYSQL = 'use '+@dbname+'
insert into ##Key_Column_usage
select '''+@dbname+'''    as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name    as CONSTRAINT_NAME
,'''+@dbname+'''    as TABLE_CATALOG
,user_name(t_obj.uid) as TABLE_SCHEMA
,t_obj.name    as TABLE_NAME
,col.name    as COLUMN_NAME
,case col.colid
  when ref.fkey1 then 1
  when ref.fkey2 then 2
  when ref.fkey3 then 3
  when ref.fkey4 then 4
  when ref.fkey5 then 5
  when ref.fkey6 then 6
  when ref.fkey7 then 7
  when ref.fkey8 then 8
  when ref.fkey9 then 9
  when ref.fkey10 then 10
  when ref.fkey11 then 11
  when ref.fkey12 then 12
  when ref.fkey13 then 13
  when ref.fkey14 then 14
  when ref.fkey15 then 15
  when ref.fkey16 then 16
end      as ORDINAL_POSITION
from
'+@dbname+'.dbo.sysobjects c_obj
,'+@dbname+'.dbo.sysobjects t_obj
,'+@dbname+'.dbo.syscolumns col
,'+@dbname+'.dbo.sysreferences ref
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('+'''F'''+ ')
and t_obj.id = c_obj.parent_obj
and t_obj.id = col.id
and col.colid   in
(ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
and c_obj.id = ref.constid
union
select
'''+@dbname+'''    as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,i.name     as CONSTRAINT_NAME
,'''+@dbname+'''    as TABLE_CATALOG
,user_name(t_obj.uid) as TABLE_SCHEMA
,t_obj.name    as TABLE_NAME
,col.name    as COLUMN_NAME
,v.number    as ORDINAL_POSITION
from
'+@dbname+'.dbo.sysobjects  c_obj
,'+@dbname+'.dbo.sysobjects  t_obj
,'+@dbname+'.dbo.syscolumns  col
,master.dbo.spt_values v
,'+@dbname+'.dbo.sysindexes  i
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('+'''UQ'''+' ,'+'''PK'''+')
and t_obj.id = c_obj.parent_obj
and t_obj.xtype = '+'''U'''+'
and t_obj.id = col.id
and col.name = index_col(user_name(t_obj.uid)+'+'''.'''+'+t_obj.name,i.indid,v.number)
and t_obj.id = i.id
and c_obj.name = i.name
and v.number > 0
  and v.number <= i.keycnt
  and v.type = '+'''P'''+''
execute sp_executesql @DYSQL

TRUNCATE TABLE FK_create_code
SET @DYSQL = 'declare cstrts cursor fast_forward read_only for
SELECT DISTINCT
   c.[TABLE_SCHEMA]
, c.[TABLE_NAME]
, u.CONSTRAINT_NAME
FROM   ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
JOIN   ##Key_Column_usage u
    ON    c.[TABLE_NAME]      = u.[TABLE_NAME]
   AND   c.[TABlE_SCHEMA]    = u.[TABLE_SCHEMA]
   AND    c.[COLUMN_NAME]     = u.[COLUMN_NAME]
JOIN   ' +@dbname+'.[INFORMATION_SCHEMA].[table_constraints] t
    ON    u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
WHERE    t.[CONSTRAINT_TYPE] = ' + '''FOREIGN KEY'''

execute sp_executesql @DYSQL
Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_Gen_FK_code_Error
  END

OPEN cstrts

fetch next from cstrts
into @schema, @tablename, @constraint

while @@fetch_status = 0
begin

SET @DYSQL = 'DECLARE @cr nchar(2), @go nvarchar(8)
SET @cr = nchar(13)+nchar(10)
SET @go = @cr + '+'''GO'''+' + @cr

SELECT DISTINCT
   @fktable = u2.[TABLE_NAME]
, @fkconstraint = r.[UNIQUE_CONSTRAINT_NAME]
, @onupdate = r.[UPDATE_RULE]
, @ondelete = r.[DELETE_RULE]
--, @column = u.[COLUMN_NAME]
FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
JOIN ##Key_Column_usage u2
    ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
JOIN ##Key_Column_usage u
    ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
WHERE r.[CONSTRAINT_NAME] = @constraint

SELECT @ColList = Null

select @ColList = COALESCE(@ColList + '+ ''','''+ ', '+'''''' +') + '
   +'''['''+' +CAST(COLUMN_NAME AS sysname)+'+''']'''+'
FROM (SELECT DISTINCT TOP 100 u.COLUMN_NAME, u.[ORDINAL_POSITION]
        FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
        JOIN ##Key_Column_usage u2
          ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
        JOIN ##Key_Column_usage u
          ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
       WHERE r.[CONSTRAINT_NAME] = @constraint
    ORDER BY u.[ORDINAL_POSITION]
) AS XXX
set @createsql =
'+ '''ALTER TABLE ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @tablename
+ '+'''] ADD CONSTRAINT ['''+'
+ @constraint
+ '+'''] '''+'
+ @cr
+ '+'''FOREIGN KEY ('''+'
+ @colList
+ '+''') REFERENCES ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @fktable
+ '+'''] ('''+'

CREATE TABLE #Ver(Dbversion varchar(2000))
INSERT INTO #Ver
SELECT @@Version

SELECT @ColList2 = Null

select @ColList2 = COALESCE(@ColList2 + '+ ''','''+ ', '+'''''' +') + '
   +'''['''+' +CAST(c.COLUMN_NAME AS sysname)+'+''']'''+'
    FROM ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
    JOIN ##Key_Column_usage u
      ON c.[TABLE_NAME] = u.[TABLE_NAME]
     AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
   WHERE u.[CONSTRAINT_NAME] = @fkconstraint
     AND u.[CONSTRAINT_SCHEMA] = c.[TABLE_SCHEMA]
ORDER BY u.[ORDINAL_POSITION]

set @createsql = @createsql + @colList2

IF EXISTS(SELECT * FROM #Ver where dbversion like '+ '''%8.00%'''+')
BEGIN

set @createsql = @createsql + '+''') ON DELETE '''+'
+ @ondelete
+ '+''' ON UPDATE '''+'
+ @onupdate
END
ELSE
BEGIN
set @createsql = @createsql + '+''')'''+'
END

INSERT INTO FK_Create_code (FK_Code)
VALUES (@createsql)

--print @createsql'

execute sp_executesql @DYSQL,N' @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
,@fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
, @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000),@ColList nvarchar(300)
, @colList2 nvarchar(300)'
,@tablename,@column,@schema, @constraint, @fktable, @fkconstraint , @onupdate, @ondelete, @comma, @createsql
, @dropsql, @truncatesql, @ColList, @colList2

Select @error_out = @@error
If @error_out <> 0
  BEGIN
   SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
   GOTO isp_Gen_FK_code_Error
  END

fetch next from cstrts
into @schema, @tablename, @constraint

end

isp_Gen_FK_code_Exit:
close cstrts
deallocate cstrts
DROP TABLE ##KEY_column_usage
RETURN @rc
SET NOCOUNT OFF

isp_Gen_FK_code_Error:

RAISERROR @Error_Type @Error_Message

GOTO isp_Gen_FK_code_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET Versus SELECT (Or, Who Really Cares Anyway)

Brett Kaiser (x002548) — Mon, 12 Feb 2007 19:36:02 GMT

EDIT: As Tara points out:

Vyas did this test quite some time ago: http://vyaskn.tripod.com/differences_between_set_and_select.htm Either I never read it, or I forgot I read it. Well hopefully I pulled some different points together differently here than Vyas did, and at the very least, I hope I made my feeling clear about a program that has to loop over 2 million times. Thanks for the heads up Tara...

OK, this always comes up from time to time, and it always seem that people are both sides of the fence. "SET is faster because....", "No, SELECT is faster because". Well there should be no debate about it, yet I've failed to find a definitive explanation of what the true story behind this is. In any case I've wanted to look into this.

In this thread the question comes up again. In that thread, Peter posts a link to a SQL Server Magazinee Article that discusses this topic. While it was a good read, I had a hard time buying it. The author (who is listed as "Reader") posted that in using SET, it is optimized and when a single value is set that this is more effecient. They then go on to say that after 1 million iterations with multiple value stes, SELECT was 59% more effecient for each operation. Now the fact that they are doing checks between each assignment seems to cause a potential unexpected interference by SQL server to the outcome of the results. Now, in above article, the first line states "Loops are fairly common in SQL Server stored procedures. ", which in itself is sort of a red herring, since if you are coding that way, I would sugest that you step back and rethink your process. If you can't find a set based solution for 99% for what you have to do, then drop me a line, or head on over to SQL Team.

So I set forth for my own test. I did my tests with an undisturbed loop where dattime values were grabbed before and after the loops of pure sets. I did for 1,000, 10,000, 1,000,000 and 10,000,000. Looking Kalen's book (Inside SQL Server 2000), she has a chapter th differences between the two, but nothing about performance. I'll need to google around some more, however, there must be an explanation about the internals. I did the test for Multiple variable assignments, and another set for single variable assignments. For the 1 million iteration (and it's really 2 million assignments) I got the following:

SELECT_MS_Multiple SET_MS_Multiple
------------------ ---------------
43470 202963

SELECT_MS_Single   SET_MS_Single
----------------                    -------------
42453                             45746

Now in both Cases, SELECT wins, in the case of Multiple assignments, SELECT seems to blow SET's doors off. Now, I need to reiterate this again. If you are finding that you have a process that needs to loop 1 million times, you either are backed into a corner due to previous developement that can't be changed, you've run into the 1% of the time that you have to, or you have a flawed application design.

If Anyone sees anyuthing wrong with this test, or if anyone has any comments I would look forward to it. With all that said, I use SELECT almost exclusively. Here's the code:

DECLARE   @SET1 int,      @SELECT1 int,      @SET2 int,           @SELECT2 int
DECLARE   @SET3 int,      @SELECT3 int,      @SET4 char,          @SELECT4 char
DECLARE   @SET5 char,     @SELECT5 char,     @SET6 char,          @SELECT6 char
DECLARE   @SET7 datetime, @SELECT7 datetime, @SET8 datetime,      @SELECT8 datetime
DECLARE   @SET9 datetime, @SELECT9 datetime, @SETA varchar(8000), @SELECTA varchar(8000)

DECLARE @x int, @s1 datetime, @s2 datetime, @e1 datetime, @e2 datetime, @c int
DECLARE @s3 datetime, @s4 datetime, @e3 datetime, @e4 datetime

SELECT @x = 1, @s1 = getDate(), @c = 1000000

WHILE @x < @c
BEGIN
SELECT    @SELECT1 = 1
  , @SELECT2 = 2
  , @SELECT3 = 3
  , @SELECT4 = 'a'
  , @SELECT5 = 'b'
  , @SELECT6 = 'c'
  , @SELECT7 = '2001-09-11'
  , @SELECT8 = GetDate()
  , @SELECT9 = '1999-12-31'
  , @SELECTA = 'This is a test of the emergency Broadcationg System. This is only a test'

SELECT    @SELECT1 = 0
  , @SELECT2 = 0
  , @SELECT3 = 0
  , @SELECT4 = ''
  , @SELECT5 = ''
  , @SELECT6 = ''
  , @SELECT7 = ''
  , @SELECT8 = 0
  , @SELECT9 = 0
  , @SELECTA = ''

SET @x = @x + 1
END

SELECT @x = 1, @s2 = getDate(), @e1 = getDate()

WHILE @x < @c
BEGIN
SET       @SELECT1 = 1
SET   @SELECT2 = 2
SET    @SELECT3 = 3
SET   @SELECT4 = 'a'
SET   @SELECT5 = 'b'
SET   @SELECT6 = 'c'
SET   @SELECT7 = '2001-09-11'
SET   @SELECT8 = GetDate()
SET   @SELECT9 = '1999-12-31'
SET    @SELECTA = 'This is a test of the emergency Broadcationg System. This is only a test'

SET       @SELECT1 = 0
SET   @SELECT2 = 0
SET    @SELECT3 = 0
SET   @SELECT4 = ''
SET   @SELECT5 = ''
SET   @SELECT6 = ''
SET   @SELECT7 = 0
SET   @SELECT8 = 0
SET   @SELECT9 = 0
SET    @SELECTA = ''

SET @x = @x + 1
END

SELECT @e2 = getDate()

SELECT @x = 1, @s3 = getDate()

WHILE @x < @c
BEGIN
SELECT @SELECT1 = 1

SELECT @SELECT1 = 0

SET @x = @x + 1
END

SELECT @x = 1, @s4 = getDate(), @e3 = getDate()

WHILE @x < @c
BEGIN
SET @SELECT1 = 1

SET @SELECT1 = 0

SET @x = @x + 1
END

SELECT @e4 = getDate()

SELECT DATEDIFF(ms,@s1,@e1) AS SELECT_MS_Multiple, DATEDIFF(ms,@s2,@e2) AS SET_MS_Multiple
SELECT DATEDIFF(ms,@s3,@e3) AS SELECT_MS_Single, DATEDIFF(ms,@s4,@e4) AS SET_MS_Single

3rd and 45? Drop back and Punt? Nah, Generate INSERTS

Brett Kaiser (x002548) — Thu, 08 Feb 2007 19:39:31 GMT

EDIT 2007/09/06: I've modified the sproc to change the dates to be formatted to 121 and stripped out all trailing spaces for char's

So, we don't have DBArtisan, but I am very happy for my copy of ERWin. Don't know what I'd do with out it.

So we have some requirements where they want to create insert statements to load a production table. I said, why not just bcp the data out in native format and create an osql script for the production DBA's to insert the data, or a sproc perhaps.

There are many better ways in my opinion, but I do like a challenge, so I wrote the following. It's not a very mature sproc...no error handling, doesn't handle images or text (how would you anyway for inserts?), ect

Just supply the table_name to the sproc

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Generate_Inserts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Generate_Inserts]
GO

CREATE PROC isp_Generate_Inserts
@TABLE_NAME sysname
AS

SET NOCOUNT ON

/*
EXEC isp_Generate_Inserts 'BusinessGroup'
EXEC isp_Generate_Inserts 'MEPType'
EXEC isp_Generate_Inserts 'Person'
EXEC isp_Generate_Inserts 'Profile'
EXEC isp_Generate_Inserts 'Status'
EXEC isp_Generate_Inserts 'SubBusinessGroup'
EXEC isp_Generate_Inserts 'XREF'
EXEC isp_Generate_Inserts 'Operator'
EXEC isp_Generate_Inserts 'FORMREF'
EXEC isp_Generate_Inserts 'MEPTERRITORY'
EXEC isp_Generate_Inserts 'MEPTICKLERSTATUS'
*/

DECLARE @INSERT varchar(8000), @COLLIST varchar(8000)
--, @TABLE_NAME sysname
, @SELECT varchar(8000), @cmd varchar(8000), @x int

SELECT @COLLIST = COALESCE(@COLLIST + ', ','') + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @INSERT = 'INSERT INTO ' + @TABLE_NAME + '('+ @COLLIST + ')'

-- SELECT @INSERT

SELECT @SELECT = COALESCE(@SELECT + '+'',''+ ','') +
           CASE WHEN DATA_TYPE
IN ('datetime','smalldatetime')
THEN + ''''+ +''''+''''+''''+'+' + 'COALESCE(CONVERT(varchar(25),' + COLUMN_NAME + ',121),'''')' + '+' + ''''+''''+''''+''''
WHEN DATA_TYPE
NOT IN ('int','bigint','smallint','tinyint','deciaml','numeric','money')
THEN + ''''+ ''''+''''+''''+'+COALESCE(REPLACE(RTRIM(' + COLUMN_NAME + ')' + ','''''''','''''''''''')' + ','''')+' + ''''+''''+''''+''''
ELSE + 'COALESCE(RTRIM(CONVERT(varchar(25),' + COLUMN_NAME + ')),'''''''''''')'
    END
    FROM INFORMATION_SCHEMA.Columns
   WHERE TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION

SELECT @SELECT = 'SELECT ' + @SELECT + ' AS DATA FROM ' + @TABLE_NAME

-- SELECT @SELECT

SET @cmd = 'CREATE VIEW XXX AS ' + @SELECT

EXEC(@cmd)

CREATE TABLE myTable99(RowId int IDENTITY(1,1), Data varchar(8000))

INSERT INTO myTable99(Data) SELECT DATA FROM XXX

SELECT 0 AS RowId, @INSERT AS DATA INTO myTemp99

SET @cmd = 'CREATE VIEW YYY AS '
+'SELECT RowId, DATA FROM myTemp99 '
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA+ ' + '''' + ' UNION ALL ' + '''' + ' AS DATA FROM myTable99 WHERE RowId < (SELECT COUNT(*) FROM myTable99)'
+'UNION ALL '
+'SELECT RowId, '+ '''' + 'SELECT ' + '''' + ' + DATA AS DATA FROM myTable99 WHERE RowId = (SELECT COUNT(*) FROM myTable99) '

-- SELECT @cmd

EXEC(@cmd)

SET @cmd = 'bcp "SELECT DATA FROM MEP.dbo.YYY ORDER BY RowId" QUERYOUT D:\MEP\Scripts\INS_'+@TABLE_NAME+'.Dat -T -c -S<servername>'

-- SELECT @cmd

EXEC master..xp_cmdshell @cmd

DROP VIEW XXX, YYY
DROP TABLE myTable99, myTemp99
SET NOCOUNT OFF

EXEC master..xp_cmdshell 'Dir D:\MEP\Scripts\*.*'

Deconstruct Text, word by word, into a single column

Brett Kaiser (x002548) — Wed, 20 Dec 2006 20:27:13 GMT

Why, I still didn't get an answer from the poster in this post.

But with Rudy laying the ground work I came up with the following.

Why anyone would need to do thids, I have no idea.  I added a twist to this version

where it counts the occurances.  The end goal here though was to find the text

row with the most occurances of any 1 word, and order the rows in that order.

Why?  I have no idea.

 
create table somewords
( id integer not null primary key identity
, blah text not null
);
insert into somewords (blah) 
values ('a word that appears maximum number of times in a column')
insert into somewords (blah) 
values ('Is it possible to get words from text columns in a sql server database')
insert into somewords (blah) 
values ('This could solve my problem if reffered column contain only single word')
insert into somewords (blah) 
values ('that''s going to require that you split out every word in the column individually')
insert into somewords (blah) 
values ('the query will definitely not be easy to write')
insert into somewords (blah) 
values ('Please read the sticky at the top of the board')
insert into somewords (blah) 
values ('The physical order of data in a database has no meaning')
GO
CREATE TABLE UniqueWords (
   Word varchar(256)
 , WordId int IDENTITY(1,1)
 , WordCount int DEFAULT(1)
 , Add_Dt datetime DEFAULT (GetDate()))
 GO
CREATE UNIQUE INDEX UnqueWords_PK ON UniqueWords(Word)
GO
CREATE PROC isp_INS_UNIQUE_WORDS
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @Words INT, @Pos INT, @x Int, @str varchar(256)
    , @word varchar(256), @start int, @end int, @exitstart int
 SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
 DECLARE myCursor CURSOR FOR SELECT Blah FROM SomeWords
 OPEN myCursor
 FETCH NEXT FROM myCursor INTO @str
 WHILE @@FETCH_STATUS = 0
   BEGIN
  WHILE (@x <> 0)
   BEGIN
    SET @x     = CHARINDEX(' ', @str, @Pos)
    IF @x <> 0
      BEGIN 
     SET @end   = @x - @start
     SET @word  = SUBSTRING(@str,@start,@end)
     IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
      INSERT INTO UniqueWords(Word) SELECT @word
       ELSE
      UPDATE UniqueWords SET WordCount = WordCount + 1 WHERE Word = @Word
     -- SELECT @Word, @@ROWCOUNT,@@ERROR
     -- SELECT @x, @Word, @start, @end, @str
     SET @exitstart = @start + @end + 1
     SET @Pos   = @x + 1
     SET @start = @x + 1
     SET @Words = @Words + 1
      END
    IF @x = 0
      BEGIN
     SET @word  = SUBSTRING(@str,@exitstart,LEN(@str)-@exitstart+1)
     IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
      INSERT INTO UniqueWords(Word) SELECT @word
       ELSE
      UPDATE UniqueWords SET WordCount = WordCount + 1 WHERE Word = @Word
     -- SELECT @Word, @@ROWCOUNT,@@ERROR
     -- SELECT @x, @Word, @exitstart, LEN(@str)-@exitstart, @str
      END
   END
  FETCH NEXT FROM myCursor INTO @str
  SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
   END 
   CLOSE myCursor
   DEALLOCATE myCursor
   SET NOCOUNT OFF
 RETURN @Words
END
GO
EXEC isp_INS_UNIQUE_WORDS
GO
SELECT * FROM UniqueWords ORDER BY Word
GO
DROP PROC isp_INS_UNIQUE_WORDS
DROP TABLE UniqueWords, somewords
GO