SQL Quickies.

Cross posted to: Maxwell's Alley.

Just a couple bits of SQL lovin for ya...

Quick search for duplicate rows. Groups up everthing that shows up in the table more than once. Good idea to do this before you define your index columns. (If you already have, and you're searching for dupes, then you've got bigger problems...)

SELECT DuplicateCount = COUNT(*),
@ColumnName
FROM
@TableName
GROUP BY
@ColumnName
HAVING COUNT(*) >= 2

Also, do YOU have input files that you have to load each day into SQL? Would YOU like a quick way to rename and archive them by date? Look no further! For a file named FILE.TXT, observe...

/* Get the two digit month... */
declare @month varchar(2)
set @month = datepart(mm,getdate())

/* get the two digit day... */
declare @day varchar(2)
set @day = datepart(dd,getdate())

/* and the four digit year. */
declare @year varchar(4)
set @year = datepart(yyyy,getdate())

/* String them all together, and create the new file name.
Note - this does not work in a single declaration. I've tried. */
declare @filename varchar(16)
set @filename = @month+@day+@year+'FILE.TXT'

/* This is the actual rename command, using the new filename. */
declare @command varchar(75)
set @command = 'ren D:\inetpub\ftproot\incoming\SQLLoads\FILE.TXT '+@filename

/* Run the command from SQL. */
exec master.dbo.xp_cmdshell @command

Voila. FILE.TXT becomes 4292005FILE.TXT. Sort n' save, baby. Note that you have to use varchar(), rather than char(). Not all timestamps are the same character length and you do NOT want SQL to pad the timestamp with spaces, as it will in char().

More later, as I fix some wacky stored procedure-ness. Good luck.

-D.