 |
Minimum value from 4 variables - datetime example.
|
| Total Hit (2068) |
This scripts shows how to obtain minimum value of four 4 datatime values contained in local variables. This method can be adapted to any datatye supported by the the SQL Server MIN function. This technique basically eliminates the writing of series of IF..ELSE or CASE statements.
....Read More |
Rating
 |
|
|
|
|
|
Getting the second recent date from a set of values.
|
| Total Hit (1809) |
ANSI & T-SQL specific solutions for getting the 2nd recent date from a set of values. This can be extended to answer nth date from a set of values but the ANSI version gets unwieldy & has to be modified for bigger values.
|
| Rating
|
|
|
|
|
|
|
|
Strip dirty characters from a numeric string.
|
| Total Hit (1732) |
A technique to search & remove dirty characters from a numeric string. This problem shows how you can simplify the solution by looking at only what is needed.
|
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Moving Average Example #1.
|
| Total Hit (2301) |
This example shows how to solve the Moving Average problem using a correlated query.
|
| Rating
|
|
|
|
|
|
|
|
|
|
How to reset Identity of Autonumber column?
|
| Total Hit (4870) |
There are two ways to reset identity field
1) Use TRUNCATE TABLE <mytable> rather than DELETE. This is a non-logged operation, however, and may not be what you need.
2) Use the DBCC CHECKIDENT command to reseed your identity value:
DBCC CHECKIDENT('mytable', RESEED, 0) -- next row inserted
....Read More |
Rating
 |
|
|
|
|
|
|
|
|
|
Complex Updates Using the Case Statement
|
| Total Hit (1699) |
«B»Introduction«/B»
One of the keys to database performance if keeping your transactions as short as possible. In this article we will look at a couple of tricks using the CASE statement to perform multiple updates on a table in a single operation.
«B»Multiple updates to a single column«/B»
....Read More |
Rating
 |
|
|
|
Updating a table from another table
|
| Total Hit (1557) |
As the article title suggests, an UPDATE statement is able to reference data in other tables. This is done using a FROM clause.
Let's use the Northwind database as our example here. A usual update statment to update a product's list price might look something like this.
«Code LangId=6»
UPDATE P
....Read More |
| Rating
|
|
|
|
Deleting Duplicate Records
|
| Total Hit (2177) |
May of us have faced duplicate records issue in SQL Server Table. First, I'll need some duplicates to work with. I use following script to create a table called dup_authors in the pubs database.
«Code LangId=6»
-- If the table exists, drop it
use pubs
go
If exists (select * from INFORMATION_SC
....Read More |
| Rating
|
|
|
|
Passing a CSV or Array to a Stored Procedure
|
| Total Hit (1880) |
This stored procedure takes two parameters @ARRAY and @SEPARATOR. It loops through the array variable and pulls out the values inside it. Right now the stored procedure just prints the values it finds. It's written so as to be easily customizable to do exactly what you need it to. Enjoy and happy pa
....Read More |
| Rating
|
|
|
|
Using FTP in Transact-SQL
|
| Total Hit (4001) |
«B»Introduction«/B»
I’ve seen a few posts asking “How do I ftp a file into SQL” Well, if you have 6.5 or 7.0 this article should be helpful. Unfortunately it’s not an easy answer, but it’ll work great once you’ve set it up. The answer is that there isn’t a function in SQL to let you do this, you
....Read More |
| Rating
|
|
|
|
|
|
|
|
Rotate a Table in SQL Server
|
| Total Hit (1654) |
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996):
Year Quarter Amount
------
....Read More |
| Rating
|
|
|
|
Create Excel XLS from T-SQL
|
| Total Hit (3991) |
This is a T-SQL script that uses OLE, ADO, Jet4 ISAM, and Linked Server to create and populate an Excel Workbook (XLS) file from T-SQL query. If the Excel Worksheet exists, the query will append to the "table".
The code is designed to be used by SQL Agent and to append to the step output with verb
....Read More |
| Rating
|
|