Fail

So yesterday I FUBARed our allocation tables. I had to get a table from Great Plains accounting synced with supporting tables in our applications database and out processing database. Truncating and reloading the database tables from the master in Great Plains turned out to be a bad idea because the apps team is actually using the ID greated in Sharepoint to match the user selections in the application. So I generated new IDs and that totally f****d up the selections. Epic fail.

Bulk Insert From a Text File

I need to bulk insert a long, ugly CSV text file delimited by pipes.

This is how I did it:

BULK INSERT
[TestServer].[StagingSchema].[StagingTable]
FROM 'C:\WeeklyRate.csv'
WITH
( FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

FIRSTROW simply tells SQL that the first row in the file is a header row and to start the import on the next row.

MAXERRORS is the number of errors I'm willing to allow before the process fails.

The FIELDTERMINATOR is whatever character the file uses to seperate fields. In my case, a pipe. If it were a tab I would put, '\t'.

The ROWTERMINATOR tells SQL where the end of the row is. It's usually a carriage return, '\n'. But you might find you have a trailing character at the end of the row. In that case, just stack the value to include the left over character. If I had a trailing pipe, my ROWTERMINATOR statement would be: FIELDTERMINATOR = '\|\n'

Female Couger vs. Male Grizzly

I've seen tigresses vs black bears and cougars vs black bears but this cougar is obviously overmatched by this huge adult, male grizzly. Yet she wins the encounter.

Derived Column Expression

I'm building an SSIS package and I want to create a derived column with hard coded value. The column is a bit type and I want to set the default value as "0".

In SSIS, I click the Derived Column component, and right click > Edit

Name the colum somthing.
Select 'add as new column>' for the second option
The expression is (DT_l1)"0"

The type will fill automatically, or you might need to set it yourself. You can leave everything else blank.

Truncate Table and Verify Truncate

SELECT COUNT(*) AS BeforeTruncateCount
FROM DBO.MyTable

TRUNCATE TABLE DBO.MyTable

SELECT COUNT(*) AS AfterTruncateCount
FROM DBO.MyTable

Copy Table w/Data Using Into

SELECT *
INTO NewTable
FROM OldTable

This will copy everything in a table into a new table, within the same database

Alter data type on a column

Since people keep asking me, here's how to force a change of datatype on a column even though SQL won't let you do this in Designer:

ALTER TABLE dbo.MyTable
ALTER COLUMN MyColulmn nvarchar(255)null

works on 2005 and 2008