Posted on June 3rd, 2008%
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 get from point A to point B without a lot of manual file copies and renames? We use the FOR command!
First, create a subdirectory for each object type (Table, StoredProcedure, UserDefinedFunction, View, Schema, Trigger, and User), then run the following from a command prompt.
for %i in (*.User.sql) do for /f “delims=., tokens=1-3″ %j in (“%i”) do move %i %k\%j.%l
for %i in (*.Schema.sql) do for /f “delims=., tokens=1-3″ %j in (“%i”) do move %i %k\%j.%l
for %i in (*.Trigger.sql) do for /f “delims=., tokens=1-3″ %j in (“%i”) do move %i %k\%j.%l
for %i in (*.sql) do for /f “delims=., tokens=1-4″ %j in (“%i”) do move %i %l\%k.%m
The command . . .
→ Read More: Separating SQL script files generated by Microsoft SQL (by type)
Posted on February 27th, 2008%
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. Here’s the process in an nutshell.
Cast the NTEXT field to the NVARCHAR(max) datatype using the CAST function.
Perform your REPLACE on the output of #1.
Cast the output of #2 back to NTEXT. (Not really required, but it does get us back to where we started.
A simple SQL query illustrates this.
select cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext)
from myntexttable
If you’re using SQL 2000, you’re out of luck, as NVARCHAR(max) first appeared in SQL 2005. However, if your NTEXT field is less than 8000 characters, you can cast it to VARCHAR(8000) — the largest possible VARCHAR size — to accomplish the same.
[Note #1: This solution below will also work with TEXT . . .
→ Read More: How to use REPLACE() within NTEXT columns in SQL Server
Posted on May 16th, 2007%
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 the field to be parsed separately (i.e. stripping the “optional” quotes).
Use consistent line endings. Pick one and stick with it for all your files. Use either (CR/LF), (LF), or (CR) — and use the same in all your files.
Put column headings in the first row only. This is more a convenience than a necessity. If you make your first row column headings, make sure it is only the first row.
Every row past the first should have an identical schema. Don’t try to be fancy and have different row types in one file. Each file should have the same number and sequence of columns.
Provide delimiters for all . . .
→ Read More: Five simple rules for creating delimited text files
Posted on May 8th, 2007%
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 annoyance is writing those XML format files. Actually, the annoyance is not writing them, it’s going through the CSV files (which, of course, are undocumented) to determine what delimiters are used in each row.
Here’s a sample of what I mean:
2934,128321,2782,”2007-04-32″,”Excluded”,2321,,22
Fortunately, most CSV files are consistent in their use of quotes, but going through dozens of columns to determine the terminators is a pain. The terminators in the above example aren’t just commas; they could also be quotes. Column three, for example, ends with a comma followed by a quote.
To generate the <record> section of my format files, I wrote the following script, which reads the first . . .
→ Read More: Automatically generate (partial) XML format files for BCP
Posted on April 3rd, 2007%
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 one the application uses.)
What’s the best way to do automate this? Ideally, I need to be able to re-run the import at any time, preferably with a single command. The process should purge the staging database, rebuild tables (since schemas may change), reload all data, and run the necessary migration scripts. Since the final product is merely a web interface to the imported data, there is no harm in wiping and recreating everything from scratch, as there is no “user data” of note. (Actually there is, but it is isolated from this process.)
The solution I came up with used a series of SQL scripts executed using sqlcmd (part of MS SQL 2005; use osql for older versions) and data import tasks using bcp (part of MS SQL), all tied together using batch files. . . . → Read More: Scripting SQL database builds
|
|
