 |
Reading Text Files Into Tables with line numbers.
|
| Total Hit (2372) |
Some times we need to read text file line by line or read a specific line using T-SQL. This article will explain a simple technique using xp_CmdShell stored proc.
Let's first create a sample file which we will import into SQL Server table.
<b>test.txt</b>
«code LangId=999»<Pre>Hello this is a
....Read More |
Rating
 |
|
|
|
How to find first/last record in the group?
|
| Total Hit (4482) |
Yes this is certainly a common situation where you have to write a query to only extract first or last record from the group.
For example you might want to extract only first product from each category. Let's look at some examples.
«code LangId=6»Drop table #TempTable
Go
create table #Te
....Read More |
Rating
 |
|
|
|
How to run *.bat file using xp_CmdShell
|
| Total Hit (6631) |
xp_CmdShell is a powerful SP if used with care. You can execute executable files (e.g. *.com, *.exe and *.bat) using xp_CmdShell however there is a little trick when executing executable file.
When you use the the following command you will get an error
<font color=red>'C:\myscript.bat' is no
....Read More |
| Rating
|
|
|
|
How to find start/end date of Previous Quarter
|
| Total Hit (3345) |
«code LangId=6»declare @PrevQtrDate datetime
declare @PrevQtrStartDate datetime,@PrevQtrEndDate datetime
declare @ReportDate datetime
set @ReportDate=getdate()
set @PrevQtrDate=dateadd(q,-1,@ReportDate)
Print @PrevQtrDate
set @PrevQtrStartDate=cast(month(@PrevQtrDate) as varchar(10)) + '
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
How to pass array to stored procedure in SQL Server
|
| Total Hit (4014) |
Have you ever come across the situaction when you have to pass mutiple values as parameter to your stored procedure.
Assume the following scenario.
- Your application pass comma seperated CustomerID list to stored procedure to get Orders for related customers.
Above requirement can be don
....Read More |
| Rating
|
|
|
|
Script - Update Blank Values of Group until Next group starts
|
| Total Hit (2641) |
Recently I came across simple scenario where I had to update some blank values of certain records. Here is what i mean
Assume you have two columns. You want to update Order# column with same order number until next group starts (e.g. ID 2,3 update with Order# => 1000).
ID Order#
1 1000
2
....Read More |
| Rating
|
|
|
|
|
|
|
|
DATEFLOOR : Rounding dates to nearest day,hour,minute,second
|
| Total Hit (7239) |
This function takes a date and a date part parameter and rounds the date down to the nearest date part.
Credit goes to "Gordon Klundt"
<b>Example:</b>
In order to round '2010-11-03 17:44:10.117' to the nearest hour ('2010-11-03 17:00:00.000')
«code LangId=6»select dbo.datefloor(cast('2010-
....Read More |
| Rating
|
|
|
|
Script to create dummy tables/procs and views for testing purpose
|
| Total Hit (5023) |
This script can generate N number of Tables/Views and Stpored Procs in few seconds with some dummy data. I use this for stress testing and create dummy tables with data.
Change @howmanytables to create N number of tables and then set
- @howmanycolumns to create N number of dummy columns per tab
....Read More |
| Rating
|
|
|
|
Shrink log files of all user databases
|
| Total Hit (5110) |
«code LangId=6»declare @ssql nvarchar(4000)
set @ssql= '
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ''''
declare @iLogFile int
declare @sLogFileName varchar(55)
declare @RecoveryModel nvarchar(10)
declare LogFiles cursor
....Read More |
| Rating
|
|
|
|
Tricky order by without writing dynamic T-SQL
|
| Total Hit (2477) |
This example shows you how to write dynamic order by statement without writing Dynamic T-SQL which is required to re-compile the execution plan every time you run it.
|
| Rating
|
|
|
|
|
|
How to find tablesize and estimated rows for all tables
|
| Total Hit (2706) |
Since there is no easy way using Enterprise Manager to know size and rows for all tables you need to write your own query. Run the following Query to see the result
«code LangId=6»USE Northwind
Go
SELECT a.Name
, SUM(b.RowCnt) EstRowCount
, SUM(b.Used) * 8 TableSize_KB
, (SU
....Read More |
| Rating
|
|
|
|
Arrays and Lists in SQL Server
|
| Total Hit (767) |
In the public forums for SQL Server, you often see people asking How do I use arrays in SQL Server? Or Why does SELECT * FROM tbl WHERE col IN (@list) not work? This text describes several methods to do this, both good and bad ones. I also present data from performance tests of the various methods.
....Read More |
| Rating
|
|
|
|
Writing Secure Transact-SQL
|
| Total Hit (702) |
There are plenty of good sources of information about how to deploy SQL Server in a secure fashion. However, these resources are often targeted at database administrators tasked with securing already developed applications. In addition, there is a rich body of information that discusses writing secu
....Read More |
| Rating
|
|
|
|
Dynamic order by
|
| Total Hit (902) |
You can use CASE statement in order by clause to select field by which you want to order the resultset. This is very handy technique.
|
| Rating
|
|