clintz' blog

Excel to SQL Server using TSQL

2006-11-21T09:52:00Z

When I create an application, I always ask my client to give me a sample data for testing my application. Almost all my client give it to me on excel file. To copy the data from excel to SQL server, what I always do was to create a new DTS package then connect to the server then connect to excel file then transform. So, when my client gave me tons of excel file, it would be a nightmare to do this over and over so I created this stored procedure to import the data from excel file to SQL Server.

create procedure _sp_ImportExcelFile
            (@Source varchar(1000)
            , @SourceSheet varchar (100)
            , @DestinationTable varchar (100))
as

declare @retval int
EXEC master..xp_fileexist @Source, @retval output -- check if file exists

if @retval = 0
            begin
                        print 'file does not exist.'
                        return
            end

if @SourceSheet is null or @SourceSheet = ''
            set @SourceSheet = '[Sheet1$]' -- assume that the Sheet name on excel file is the default name
else
            set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
            set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @Source + ''', ' + @SourceSheet + ')')

Tip for faster T-SQL coding

2006-09-27T06:32:00Z

Lots of developers write code in flash with the help of intellisense. But when coding t-sql is scripts, there is no built-in intellisense on query analyzer or SSMS. You will have to use a 3rd party tool to have intellisense on your query analyzer.
But if you can't find 3rd party intellisense tool usefull 'cause some tools eats all your memories or something, there's a lot of cool stuff in query analyzer or SSMS, like using the object browser and dragging the column folder of the table to list all the columns seperated by a comma.

And also you can use a keyboard shortcut.
In Tools>Customize on QA or Tools>Options>Environment>Keyboard on SSMS, you can write a sql statement and assign it on a shortcut ket that will execute whenever you press the shortcut key.

For example:
Input the statement "select [name] from sysobjects where xtype = 'S'" on CTRL+5 and it will list all the stored procedure when you press CTRL+5.
The cool part is, when you highlight a word on the editor, it will serve as a parameter at the sql statement you write on a shortcut key. Like when you put sp_helptext on one of the shortcut keys, you can just highlight a SP or UDF or View name on the editor and press the shortcut key to view the code.
here's the sql statements I used on the shortcuts.

CTRL+3 -- list all the tables
select TableName=o.name, [Rows]=max(i.[rows]),o.id,(select count(*) from syscolumns where id=o.id) as ColCount from sysobjects o join sysindexes i on o.id=i.id where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable')=1 group by o.name, o.id order by TableName

CTRL+4 -- list all the views
select Table_Name, 'VIEW' as Type from INFORMATION_SCHEMA.Tables where Table_type = 'VIEW' order by Table_Type

CTRL+5 -- list all the SP
select [name], 'Stored Procedure' as Type from sysobjects where xtype = 'P' and status >= 0 order by type desc, [name]

CTRL+6 -- list all the UDF
select [name], 'Function' as Type from sysobjects where xtype in ('TF','FN','IF') and status >= 0 order by type desc, [name]

CTRL+7 -- for viewing the code of SP, UDF, View (requires parameter {highlighted text on editor})
sp_helptext

CTRL+8 -- to list all the column of a table (requires tablename parameter {highlighted text on editor})
sp_MShelpcolumns

I also create a SP:

CREATE PROCEDURE _sp_Select_From
   @objTable varchar(255)
   , @Order1 varchar(255) = 1
   , @Order2 varchar(255) = 2
   , @Order3 varchar(255) = 3
as
   exec ('select * from ' + @objTable + ' order by ' + @Order1 + ', ' + @Order2 + ', ' + @Order3)

and assign it on CTRL+9 to show the data on a table.

And I created a SP:

CREATE PROCEDURE _sp_PrintColumns
   @ObjName varchar(8000)
as

   set nocount on
   declare @TotalParams int, @cntr int, @ObjID int, @ColName varchar(50), @ColValue varchar(50), @ColTypeID int, @ColType varchar(50), @TempColumnName varchar(50)

   set @ObjID = (select [id] from [dbo].sysobjects where [name] = @ObjName)

   if @ObjID is NULL or @ObjID = ''
       begin
           select 'object not found'
           return
       end

    set @TempColumnName = ''
    set @TotalParams = (select top 1 colid from [dbo].syscolumns where [id] = @ObjID order by colid desc)
    set @cntr = 1

   print '--' + @ObjName
   while @cntr <= @TotalParams
       begin
            set @TempColumnName = @ColName
            select top 1 @ColName = [name]
                , @ColTypeID = xtype
            from [dbo].syscolumns where [id] = @ObjID and colid = @cntr
            set @ColType = (select distinct top 1 [Type_Name] from master.dbo.spt_datatype_info where ss_dtype = @ColTypeID)

           if @TempColumnName<> ''
               print ', ' + @ColName
           else
               print @ColName

            set @cntr = @cntr + 1
       end

and assign it on CTRL+0 to list all the columns of a table or parameters of SP and UDF seperated by a newline and a comma.

And it helps me a lot and makes me more productive.

Creating Sudoku problems using TSQL

2006-09-05T08:34:00Z

When I'm bored at the office or home, I sleep.
One rainy afternoon, when I can't find the bug on my application, I feel kinda bored but I don't want to sleep because my manager was pissed. So i went over to the next cubicle to chat with my sleepy officemate and found out that she was playing Sudoku.
I asked her to explain to me how that game works. And I find it interesting.
After playing some 5 or 6 games, I thought I can create problems for this game using t-sql, just create a lot of random numbers then copy-paste the result in excel.

So there I was, creating a sudoku problem script. And here's what I've done.
You can also try it. Just run the script and copy-paste the result in excel and that's it..

/*****Sudoku*******/
declare @tbl table (id int identity, a char(1), b char(1), c char(1), d char(1), e char(1), f char(1), g char(1), h char(1), i char(1))
declare @tblCoordinatesAndValue table (x int, y int, value int)
declare @i int, @tmpNo int, @x int, @y int, @v char(1), @isOkX int, @strValues varchar(50), @ypos int
set @i = 0
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')
insert into @tbl (a, b, c, d, e, f, g, h, i) values ('', '', '', '', '', '', '', '', '')

declare @min int, @max int
select @min = 1, @max = 9

while @i < 80
   begin
       set @strValues = ''
       select @tmpNo = floor((((@max + 1) - @min) * RAND()) + @min)
       set @x = @tmpNo
       select @tmpNo = floor((((@max + 1) - @min) * RAND()) + @min)
       set @y = @tmpNo
       select @tmpNo = floor((((@max + 1) - @min) * RAND()) + @min)
       set @v = @tmpNo

       if not exists (select * from @tblCoordinatesAndValue where x = @x and y = @y)
           begin
               insert into @tblCoordinatesAndValue (x, y, value) values (@x, @y, @v)

               if @y >= 1 and @y <= 3
                       set @ypos = 1
               if @y >= 4 and @y <= 6
                       set @ypos = 4
               if @y >= 7 and @y <= 9
                       set @ypos = 7
               if @x >= 1 and @x <= 3
                   select @strValues = @strValues + tblTemp.tmp from
                       (select top 3 convert(varchar, a) [tmp] from @tbl where id >= @ypos
                           union all select convert(varchar, b) from @tbl where id >= @ypos
                           union all select convert(varchar, c) from @tbl where id >= @ypos) tblTemp
               if @x >= 4 and @x <= 6
                   select @strValues = @strValues + tblTemp.tmp from
                       (select top 3 convert(varchar, d) [tmp] from @tbl where id >= @ypos
                           union all select convert(varchar, e) from @tbl where id >= @ypos
                           union all select convert(varchar, f) from @tbl where id >= @ypos) tblTemp
               if @x >= 7 and @x <= 9
                   select @strValues = @strValues + tblTemp.tmp from
                       (select top 3 convert(varchar, g) [tmp] from @tbl where id >= @ypos
                           union all select convert(varchar, h) from @tbl where id >= @ypos
                           union all select convert(varchar, i) from @tbl where id >= @ypos) tblTemp

               if charindex(convert(varchar, @v), @strValues) = 0
                   begin
                       set @isOkX =
                       (select (case when @x=1 then a when @x=2 then b when @x=3 then c
                                       when @x=4 then d when @x=5 then e when @x=6 then f
                                       when @x=7 then g when @x=8 then h when @x=9 then i end)
                       from @tbl
                       where (case when @x=1 then a when @x=2 then b when @x=3 then c
                                       when @x=4 then d when @x=5 then e when @x=6 then f
                                       when @x=7 then g when @x=8 then h when @x=9 then i end) = @v)

                       if isnull(@isOkX, 0) = 0
                           begin
                               if not exists(select * from @tbl where (a=@v or b=@v or c=@v or d=@v or e=@v or f=@v or g=@v or h=@v or i=@v) and (id = @y))
                                   begin
                                      update @tbl
                                       set a = (case when @x = 1 then @v else a end)
                                           , b = (case when @x = 2 then @v else b end)
                                           , c = (case when @x = 3 then @v else c end)
                                           , d = (case when @x = 4 then @v else d end)
                                           , e = (case when @x = 5 then @v else e end)
                                           , f = (case when @x = 6 then @v else f end)
                                           , g = (case when @x = 7 then @v else g end)
                                           , h = (case when @x = 8 then @v else h end)
                                           , i = (case when @x = 9 then @v else i end)
                                       where id = @y
                                   end
                           end
                   end
           end

       set @i = @i + 1
   end

select a, b, c, d, e, f, g, h, i from @tbl

/******************/

If you can optimized the script, the better..

Creating Dummy Data

2006-08-31T05:55:00Z

Testing is one of the "must" in software development process.
Once, our client lost almost 14 million pesos in a month because of using an application that was developed in a hurry and tested only for 2 days. I feel sorry for them because i was the one who developed that application. I warned them though, that they must have a test run for atleast a month or two.
Anyway, when testing some application, you will need a dummy data.The most common dummy data that were used are Integer/Float, String and Date.

To create a random integer or float, you can use the RAND() function (from BOL: RAND() function returns a random float value from 0 through 1), but sometimes I get a value more than 1.
So, if you want to generate a random integer which is less than n (n is any integer), all you have to do is multiply the result of RAND() function to n.
So when you run,
   select rand() * 100
the result is always < 100.
When you want to generate a random number between two numbers, you can can create a query something like this

   declare @min int, @max int
   select @min = 10, @max = 20

   select (((@max + 1) - @min) * RAND()) + @min

To create a random character you can use CHAR() function.
This query will generate a random 10-letter string (capital letters)

   declare @min int, @max int, @nRand int, @cRand varchar(10)
   select @min = 65, @max = 90 -- (97 - 122 --> small letters)

   declare @i int
   set @i = 0
   set @cRand = ''
   while @i < 10
       begin
           select @nRand = (((@max + 1) - @min) * RAND()) + @min
           select @cRand = @cRand + char(@nRand)
           set @i = @i + 1
       end

   select @cRand

At first, i find it hard on generating a random date. When you try

   select cast(0 as datetime) -- it will return 1900-01-01

If that's the case, then all you have to do to generate a random date is generate a random number.
In Sql server, i think the full range of dates it can handle is from 01/01/1900   to 12/31/9999.
The average days in a year is 365.2422.

   select cast(365.2422 as datetime) -- will return 1901-01-01
   select cast(365.2422 * 100 as datetime) -- will return 2000-01-01
   select cast(365.2422 * 100 + 13 as datetime) -- will return 2000-01-14

Anyway, if you want to generate a random date in between two dates, you can try this script:

   declare @nRand int
    declare @Jan float, @Feb float, @Mar float, @Apr float
       , @May float, @Jun float, @Jul float, @Aug float
       , @Sep float, @Oct float, @Nov float, @Dec float

   select @Jan = 31
   , @Feb = @Jan + 28.2422
   , @Mar = @Feb + 31
   , @Apr = @Mar + 30
   , @May = @Apr + 31
   , @Jun = @May + 30
   , @Jul = @Jun + 31
   , @Aug = @Jul + 31
   , @Sep = @Aug + 30
   , @Oct = @Sep + 31
   , @Nov = @Oct + 30
   , @Dec = @Nov + 31

   -- select @Jan jan, @Feb feb, @Mar mar, @Apr apr
   --    , @May may, @Jun jun, @Jul jul, @Aug aug
   --    , @Sep sep, @Oct oct, @Nov nov, @Dec [dec]

   --to generate a random date between Aug 2005 and Feb 2005:
   select @nRand = ((@Aug + 1) - @Feb) * RAND() + @Feb
   select convert(varchar, cast((365.2422 * 105) - 31 + @nRand as datetime), 101)

There's a lot of way to create a random data in sql server. But those are some of the basic ways i know.