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 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
Posted on September 7th, 2004%
I use my laptop (a highly-recommended Toshiba Portege M100) for most of my development work. Typically, I develop on my laptop (a sandbox environment), then push data to another server (either a staging or production server). One of the problems I run into is my ever-changing physical location. When I’m at home, certain Web sites need internal IP addresses; at other places, they need external IP addresses.
The solution to this problem is by toggling the use of your HOSTS file. I enterd all the static entries into my HOSTS file, and created a small batch file to “toggle” the HOSTS file on and off. (When the HOSTS file exists, it is used, so renaming it to something other than HOSTS – with no file extension – turns it “off”.)
The batch file for toggling the HOSTS file is:
if exist c:\windows\system32\drivers\etc\hosts goto :hosts_off :hosts_on ren c:\windows\system32\drivers\etc\hosts.toggle hosts . . .
→ Read More: Toggling your HOSTS file
Posted on April 7th, 2004%
A coworker had an odd little requirement that they asked me if I could help with. She has image files stored in a directory structure, and she wanted to be able to pull the files out and rename them with the parent directories. Confused? I was, too. Essentially they want a file whose path is \A\01\23\45.tif to be renamed to A012345.tif.
Two steps to solving this. First, I needed a script file that would make a copy of a file and rename it by prepending the names of its parent folders to the filename. Second, I needed a script file which would find every file under a given subdirectory and call the first script file for each file found.
The first script file is a Windows Script Host file, written in VBScript. I name this file sdrename.vbs (as in subdirectory rename). The basic flow is:
Retrieve the full, absolute path to the file from command line arguments.
Determine the root folder . . .
→ Read More: Renaming files in a subdirectory tree
|
|
