case statement

Access has an IIF statement, SQL has the case. Want to display your bit data as "yes" or "no"? Enter the case statement! Example:


SELECT thisVariable, thatVariable,
CASE
WHEN likesApples = 1 THEN 'yes'
WHEN likesApples = 0 THEN 'no'
WHEN likesApples = 2 THEN 'not sure'
else 'not answered'
END AS appleVariable
FROM thatTable


sqlbulkcopy

I have just used sqlbulkcopy for the third or so time. This is a handy thing built into 2.0 that you should be familiar with. In the past I have used it in my code to copy a row into an "archive" table before deleting it from the initial table. Today I used it to copy data to a database housed at another school. I had a copy of their schema. I built a view of our data, such that the view held just what needed to go into their table. Here is my sample code:

string sql = "SELECT * FROM sourceView";
            using (SqlConnection conn = new SqlConnection(dsn))//source dsn
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            using (SqlBulkCopy copy = new SqlBulkCopy(dsn2))//destination & source dsn are the same
            {
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                copy.DestinationTableName = "destinationTable";
               //have to map the columns because the tables are not an exact match
                copy.ColumnMappings.Add("fieldname", "samefieldButSlightlyDifferentName");
                copy.ColumnMappings.Add("fieldname2", "samefieldButSlightlyDifferentName2");
                copy.WriteToServer(rdr);
                conn.Close();
            }

A couple things to note. I copied code from the last time I did this. Reading my comment I'm not sure I actually needed to map out the columns, because from my view to the destination the schemas matched up. Last time I did this however, there were a few more columns in the destination table, hence my comment about the not matching up. Second, I got this error early on: SqlBulkCopy: the locale id '0' of the source column ' ' and the locale id '1033' of the destination column '' do not match. This was because the ID column in my table was an int and in theirs was varchar. I therefore modified my view for that and one other column so that the data types matched using CAST(field as varchar) AS field.