x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

SQL Gimmicks

This is for some code that seems more like puzzles than having any real life reason behind them....
SET BASED Number Table Create Hack

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...

posted @ Friday, October 29, 2010 12:29 PM | Feedback (3) | Filed Under [ SQL Gimmicks ]

Add Foreign Keys Back to the Database

"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...

posted @ Thursday, March 01, 2007 9:49 AM | Feedback (0) | Filed Under [ SQL Gimmicks ]

Collecting Requirements For Key Information

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...

posted @ Thursday, February 22, 2007 1:05 PM | Feedback (0) | Filed Under [ SQL Gimmicks ]

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

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...

posted @ Tuesday, February 20, 2007 11:58 AM | Feedback (2) | Filed Under [ SQL Gimmicks ]

Deconstruct Text, word by word, into a single column

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...

posted @ Wednesday, December 20, 2006 3:27 PM | Feedback (2) | Filed Under [ SQL Gimmicks ]

Powered by:
Powered By Subtext Powered By ASP.NET