Madhivanan

Find Nth Maximum value

"How do I find Nth maximum value?" is one of the most asked questions

Here are some methods

I explain how to find 5th Maximum value

Create table number (num integer)
Insert into number values(3456)
Insert into number values(846)
Insert into number values(1456)
Insert into number values(3098)
Insert into number values(34)
Insert into number values(67856)
Insert into number values(906)
Insert into number values(34656)
Insert into number values(9056)
Insert into number values(3036)

(1) Use Inner Join

select t1.num from number t1 inner join number t2 on t1.num<=t2.num
group by t1.num having count(t1.num)=5

(2) Use Top Operator

Select top 1 num from(
Select top 5 num from number order by num desc) T
order by num asc

(3) Generate Serial No based on descending order of the values

select num from (
Select (select count(*) from number where num>=T.num)as Sno ,num
from number T ) temp where Sno=5

(4) Use Aggregate Function

Select min(num) from (select top 5 num from number order by num desc) T

Now you can replace 5 to the number that you want to find the maximum value

Import/Export to Excel

Apart from using DTS and Import/Export wizard, we can also use this query to export data from SQL Server2000 to Excel and vice versa

To export data from SQL Server table to Excel file, create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Generate SQL Script

--This procedure will generate the structure of the table

Create Procedure GenerateScript (           
@tableName varchar(100))           
as           
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)           
Begin            
declare @sql varchar(8000)            
declare @table varchar(100)            
declare @cols table (datatype varchar(50))          
insert into @cols values('bit')          
insert into @cols values('binary')          
insert into @cols values('bigint')          
insert into @cols values('int')          
insert into @cols values('float')          
insert into @cols values('datetime')          
insert into @cols values('text')          
insert into @cols values('image')          
insert into @cols values('uniqueidentifier')          
insert into @cols values('smalldatetime')          
insert into @cols values('tinyint')          
insert into @cols values('smallint')          
insert into @cols values('sql_variant')          
           
set @sql=''            
Select @sql=@sql+             
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +             
case when Data_Type in (Select datatype from @cols) then '' else  '(' end+
case when data_type in ('real','money','decimal','numeric')  then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar)       else '' end+
case when Data_Type in (Select datatype from @cols)then '' else  ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end           
from Information_Schema.COLUMNS where Table_Name=@tableName            
 
  
select  @table=  'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName           
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'           
select @sql  as DDL         
  
End           

Else        

Select 'The table '+@tableName + ' does not exist'           

Select Data from Top N Columns

Suppose you have table with many columns and often you need data from first 15 or 20 columns. You have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want

Here is the procedure

CREATE procedure TopNcolumns (@tableName varchar(100),@n int) 
as   
Declare @s varchar(2000)   
set @s='' 
If @n>=0  
Begin 
set rowcount @n 
Select @s=@s+','+ column_name from information_schema.columns  
where table_name=@tablename order by ordinal_position 
Set rowcount 0 
Set @s=substring(@s,2,len(@s)-1) 
Exec('Select '+@s+' from '+@tablename) 
End 
else 
Select 'Negative values are not allowed' as Error 

If you execute TopNColumns 'Mytable',12  then first 12 columns with data from the table Mytable will be displayed

Backup Database

This query will backup the current database

declare @sql varchar(1000)
select @sql = 'BACKUP DATABASE '+(select db_name())+' TO DISK = ''E:\'+(select db_name())+
convert(varchar,GETDATE(),112)+'.bak'''
Exec(@sql)

If the database name is Test then executing this query will backup Test database and name of that backup file is Test20050817.bak. Current date with the format yyyymmdd will be suffixed to the DBName

If you schedule this query to run daily, then backup will be generated daily as a seperate file

Generate Random Numbers

This procedure will display n random numbers selected from specified range

Pass Starting Number, Ending Number and the number of numbers you want to select randomly

Create Procedure RandomNumbers (@min bigint, @max bigint, @count int)
as
Set Nocount On

Declare @t table(num bigint)
Declare @start bigint
Declare @End bigint

While @min<=@max

Begin
Insert into @t values(@min)
set @min=@min+1
End

Set Rowcount @count
Select num from @t order by newid()
Set Rowcount 0

If you run the below code

Exec RandomNumbers 100,300,10

10 random numbers between 100 and 300 will be displayed

Scripts Stored Procedures and Functions through query

This query will script the Procedures and Functions

All you have to do is replace DBname by the actual Database Name

exec master..xp_cmdshell
'bcp "Select routine_definition from DBname.information_Schema.routines order by routine_name" queryout "C:\scripts.sql" -c'
 

After it runs successfully, the file C:\scripts.sql will have the scripts

 

BCP - Export data to Text File

Here is a simple method of exporting all the data from SQL Server table to a Text File

CREATE Procedure BCP_Text_File
( 
@table varchar(100), 
@FileName varchar(100) 
) 
as 
If exists(Select * from information_Schema.tables where table_name=@table)
    Begin
        Declare @str varchar(1000) 
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c''' 
        Exec(@str) 
    end
else
    Select 'The table '+@table+' does not exist in the database'

Execute this procedure by giving Table Name and the File Name

EXEC BCP_Text_File 'Employee','C:\emp.txt'

Now all the data from Employee table will be exported to the text file which will be located at C:\emp.txt

Get IP Address

Here is a method of getting the IP Address of a system using Stored Procedure

Create Procedure Get_IPAddress
(@sysName varchar(30))
as
 Set Nocount On

 Create table #t (data varchar(100))
 Declare @sql varchar(100)
 Set @sql='Ping -n 1 '+@sysname

 Insert into #t
 Exec Master..xp_cmdShell @sql
 
 If exists (select * from #t where data like '%Unknown%')
  Select 'The system '+@sysName+' doesnt exist' as Error
 else
  Select Substring(data,charindex('[',data,1)+1,charindex(']',data,1)-(charindex('[',data,1)+1))
  as IPAddress from #t where data like '%bytes of data%' 
 
 drop table #t
 Set Nocount Off

Run this procedure as

EXEC Get_IPAddress 'systemName'

Storing Images

I have read many questions in the Forums asking "How do I store images in the table?". The simplest answer would be to store only the physical path of the images in the table and storing the images in the server's physical directory. To retrieve the image use Presentation layer's File System object. This makes easy the process of storing and retrieving the images.Otherwise AppendChunk and GetChunk methods should be used in the Front End Application.

Here are the complete explanations on how to handle images

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;317016

CSV to Multiple Columns

If the table is denormalised and has Comma Seperate Values in a

column, this code will copy it to Multiple columns of a

Normalised table

declare @DeNormalisedTable table(data varchar(8000))

insert into @DeNormalisedTable 
select '1,Davolio,Nancy' union all
select '2,Fuller,Andrew' union all
select '3,Leverling,Janet' union all
select '4,Peacock,Margaret' union all
select '5,Buchanan,Steven' union all
select '6,Suyama,Michael' union all
select '7,King,Robert' union all
select '8,Callahan,Laura' union all
select '9,Dodsworth,Anne'

select * from @DeNormalisedTable -- Comma Seperated Values
declare @s varchar(8000), @data varchar(8000)

Create table #NormalisedTable (Code int, FirstName varchar(100), 

LastName varchar(100))

select @s=''

while exists (Select * from @DeNormalisedTable where data>@s)
Begin
        Select @s=min(data) from @DeNormalisedTable where data>@s
        select @data=''''+replace(@s,',',''',''')+''''
        insert into #NormalisedTable	
        exec('select '+@data)
End

select * from #NormalisedTable -- Data in Normalised Table

drop table #NormalisedTable

Enhanced ISNUMERIC() function

It seems often users want to check whether the data has only numbers in a varchar type column. The commonly suggested one is to make use of ISNUMERIC() function. But the problem in using that function is that it will treat some alphabets, $, char(10),etc as numbers.

Cosider this example

declare @test table(strings varchar(50))
Insert into @test
 Select '12d3' union all
 Select '87234.45' union all
 Select '$123,456.00' union all
 Select '  12  ' union all
 Select char(10) union all
 Select '$'
select strings,isnumeric(strings)as valid from @test

The result is

strings                                                         valid      
-------------------------------------------------- -----------
12d3                                                           1
87234.45                                                    1
$123,456.00                                               1
  12                                                            1

                                                                  1
$                                                                1

Note that only 87234.45 should be considered as numeric and not others

The following function will solve this problem

CREATE Function Is_numeric(@value varchar(25))
Returns bit
as
Begin
Return
    (
    case when @value not like '%[^-0-9.]%' and len(@value)-len(replace(@value,'.',''))<2
        and
            1=
                (
                case when charindex('-',@value)>0 then
                    case when left(@value,1)='-' and len(@value)-len(replace(@value,'-',''))<2 and len(@value)>1 then
                        1
                    else
                        0
                    end
                else
                    1
                end
              ) then

           1
     else
           0
     end
)
End

Now execute this select statement

select strings, dbo.is_numeric(strings)as valid from @test

The result is

strings                                                         valid
-------------------------------------------------- -----
12d3                                                           0
87234.45                                                    1
$123,456.00                                               0
  12                                                            0

                                                                   0
$                                                                 0

SQLAnswers

This freeware tool is used to execute sql,procedures,etc and export the result to EXCEL,PDF,HTML,etc format

Try it here

http://www.sqlanswers.com/Software/

Dynamic Crosstab with multiple PIVOT Columns

CREATE procedure CrossTab
(
 @select varchar(2000),
 @PivotCol varchar(100),
 @Summaries varchar(100), 
 @GroupBy varchar(100),
 @OtherCols varchar(100) = Null
)

AS

set nocount on
set ansi_warnings off

declare @sql varchar(8000)

Select @sql = ''
Select @OtherCols= isNull(', ' + @OtherCols,'')

create table #pivot_columns  (pivot_column_name varchar(100))
Select @sql='select ''' + replace( + @PivotCol,',','''   as pivot_column_name union all select ''')+''''
insert into #pivot_columns
exec(@sql)

select @sql=''

create table #pivot_columns_data  (pivot_column_name varchar(100),pivot_column_data varchar(100))

Select @PivotCol=''
Select @PivotCol=min(pivot_column_name) from #pivot_columns
While @PivotCol>''
Begin
 insert into #pivot_columns_data(pivot_column_name,pivot_column_data)
 exec  (
  'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from
   ('+
    @select
   +'
   ) T'
  )
 Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol

end

select @sql = @sql + ', ' +
  replace(
   replace(
    @Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' +
    pivot_column_data +  ''' THEN '
    ),
   ')[', ' END) as [' + pivot_column_data
   )
from #pivot_columns_data
order by pivot_column_name

exec ( 'select ' + @GroupBy +@OtherCols +@sql +

       ' from (
  '+
   @select
  +'
  ) T
 GROUP BY ' + @GroupBy)

drop table #pivot_columns
drop table #pivot_columns_data

set nocount off
set ansi_warnings on

Now execute the procedure by supplying two pivot columns shipcountry and Year(Orderdate)

EXEC CrossTab
'SELECT LastName, OrderDate,shipcountry FROM northwind..Employees Employees
 INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcountry,Year(OrderDate)',
'Count(LastName)[]',
'LastName'

Truncate All Tables - Part I

This procedure will truncate all the tables in the database
To truncate all tables,I have seen the programmers using Cursors to get all tables and truncate them.This will avoid Cursors. Running this procedure will make SQL Server to slow down other tasks. So test this with test server and make sure that you are not running any other SQL Server related processes

 


Create Procedure Truncate_All_Tables
as
Declare @t table(query varchar(1000),tables varchar(50))

Insert into @t 
    select 'Truncate table ['+T.table_name+']', T.Table_Name from 
    INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name 
    where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and 
    T.table_name not in ('dtproperties','sysconstraints','syssegments') and 
    Table_type='BASE TABLE' 
 

Insert into @t 
    select 'delete table ['+T.table_name+']', T.Table_Name from 
    INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and 
    T.table_name <>'dtproperties'and Table_type='BASE TABLE' 

Declare @sql varchar(8000)

Select @sql=IsNull(@sql+' ','')+ query from @t

Exec(@sql)

GO

Run this procedure as

EXEC Truncate_All_Tables