Skip to content

Separating SQL script files generated by Microsoft SQL (by type)

There’s one great feature in SQL: the “Generate Scripts” command. Unfortunately, it has one limitation: the default filenames of scripts look something like this:
dbo.fnc_PlayerValue.UserDefinedFunction.sql
dbo.UserSelect.StoredProcedure.sql
I’d much prefer the filenames to match the object name, without the owner (’dbo’) or object type. In other words, I’d prefer the above two files to look like this:
UserDefinedFunction\fnc_PlayerValue.sql
StoredProcedure\UserSelect.sql
How do we [...]

Posted 03 June 2008 § Technology ‡ Comments (1)

How to use REPLACE() within NTEXT columns in SQL Server

SQL has an incredibly useful function, REPLACE(), which replaces all occurrences of a specified string with another string, returning a new string. It works great with all forms of NCHAR and NVARCHAR fields. It does not, however, work with NTEXT fields.
Fear not — there’s an easy workaround, thanks to type-casting and SQL 2005’s NVARCHAR(max) datatype. [...]

Posted 27 February 2008 § Uncategorized ‡ Comments (23)

Five simple rules for creating delimited text files

Here’s a few tips for those people who provide raw data in text files (think CSV files and the like).

Surround all text fields in single quotes, even if a comma does not exist in a specific instance. By failing to do this, you lose field delimiter consistency on a row-by-row basis, forcing the contents of [...]

Posted 16 May 2007 § Uncategorized ‡ Comments (0)

Automatically generate (partial) XML format files for BCP

I’ve been working with a lot of raw data files lately — data files that come in some delimited format which I must move into a SQL database. Most of the files are CSV files, and some of them have over 100 columns.
Using BCP to import CSV files into SQL is a pleasure. The only [...]

Posted 08 May 2007 § Uncategorized ‡ Comments (0)

Scripting SQL database builds

A project I’m working on requires importing data from hundreds of megabytes of delimited text files into a staging database, then converting and migrating the data into an operations database. (Forgive the naming conventions. The “staging” database is used as an intermediary between the text files and the final database. The “operations” database is the [...]

Posted 03 April 2007 § Uncategorized ‡ Comments (0)