Monthly Archives: February 2012

SQL Server: Bulk updates, bulk inserts and table valued parameters for arrays

When writing bulk updates or inserts into Oracle with ODP.NET you can use PLSQLAssociativeArray (example here) and you may search around for an equivalent array type in SQL Server but there isn’t one. Firstly this is disappointing, then you kind of get used to the idea, hopefully this post will help you on onto this path.
First off I thought it was wrong not to have an array type, “Oracle does, this is stupid” I thought. After a cup of tea, I put a couple of seconds thought into this and the best I could come up with is all the data we use in SQL is tabular data anyway and you could represent a PLSQLAssociativeArray as a table with one column right? So is it any loss? No, of course not, but the programmer in me is used to dealing with arrays.
However there is a drawback that I haven’t completely got over yet, and that is that you must define the type name of the SqlParameter, this must be a valid type, so randomly creating some SQL and firing it at the database isn’t an option. But I have other options in my mind, here’s some examples of bulk operations utilizing table valued parameters:

Create a table to manipulate:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Greetings](
	[PrimaryId] [int] NOT NULL,
	[FirstValue] [nchar](10) NULL,
	[SecondValue] [nchar](10) NULL
) ON [PRIMARY]

GO

Create a loosely typed DataTable and populate it with some data to insert into the table:

DataTable dt = new DataTable("Tim");
dt.Columns.Add(new DataColumn("FirstCol", typeof(int)));
dt.Columns.Add(new DataColumn("SecondCol", typeof(String)));
dt.Columns.Add(new DataColumn("ThirdCol", typeof(String)));

DataRow r1 = dt.NewRow();
r1["FirstCol"] = 1;
r1["SecondCol"] = "Hello";
r1["ThirdCol"] = "Hi";
dt.Rows.Add(r1);

DataRow r2 = dt.NewRow();
r2["FirstCol"] = 2;
r2["SecondCol"] = "Goodbye";
r2["ThirdCol"] = "Bye";
dt.Rows.Add(r2);

(brings back memories of .NET 1.1 doesn’t it)

Next up create a table valued type in SQL Server, of course you could keep well defined types in your database, but for this example I just want something “temporary”. Also this could be a generated string from the DataTable, look around someone might already be doing this, else it’s easy enough to roll your own

using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "BEGIN TRY"
        + " CREATE TYPE dbo.T1"
        + " AS TABLE ( [FirstCol] [int] NOT NULL, [SecondCol] [nchar](10) NULL, [ThirdCol] [nchar](10) NULL )"
        + " END TRY"
        + " BEGIN CATCH"
        + " END CATCH";
    cmd.ExecuteNonQuery();
}

So by this point in execution your app would have a type defined called dbo.T1, you could of course use a temporary generated name for this too. Note the TRY CATCH in there; ignoring errors.

Next do a bulk insert: (Note: SqlBulkCopy may be quicker, I’ve not checked)

using (SqlCommand cmd = conn.CreateCommand())
{
    SqlParameter p = new SqlParameter("@MyInputDT", SqlDbType.Structured);
    p.Value = dt;
    p.TypeName = "dbo.T1";
    cmd.Parameters.Add(p);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "INSERT INTO dbo.Greetings (PrimaryId, SecondValue)"
        + " SELECT FirstCol, ThirdCol"
        + " FROM @MyInputDT";
    cmd.ExecuteNonQuery();
}

This to me is better than passing two arrays, a size parameter and looping though and inserting each. But we’ve got SqlBulkCopy, so you may not want to use this approach.

By this point in execution there would be two new entries in the dbo.Greetings table but I’ve deliberately missed out the FirstValue, so that will be NULL.

Let’s do a bulk update to fix it, also remove the “temporary” table valued type:

using (SqlCommand cmd = conn.CreateCommand())
{
    SqlParameter p = new SqlParameter("@MyUpdDT", SqlDbType.Structured);
    p.Value = dt;
    p.TypeName = "dbo.T1";
    cmd.Parameters.Add(p);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "UPDATE Greetings"
        + " SET FirstValue = SecondCol"
        + " FROM @MyUpdDT"
        + " WHERE PrimaryId = FirstCol"
        + " BEGIN TRY"
        + " DROP TYPE dbo.T1"
        + " END TRY"
        + " BEGIN CATCH"
        + " END CATCH";
    cmd.ExecuteNonQuery();
}

So there’s a bulk insert and bulk update, I think this is pretty neat, but the requirement on having a well defined type is a gripe, now I’m over the lack of array type I want a var type now, shame.

See also Table-Valued Parameters in SQL Server 2008 (ADO.NET)

Better late than never; summer tyres fitted

Schwalbe Marathon GreenGuard fitted (26″ x 1.5″):

They are considerably lighter than the Marathon Plus tyres and cheaper too.
My bike feels lighter, more responsive and I’ll let you know if I get a huge increase in punctures now I’m not running Marathon Pluses.

Reflective vs cat eyes #cycling #cats

My pesky cat got on top of the wardrobe and sat in the middle of the tyre that is on top of the wheel box, before getting her down I took a photo:

Next to her you can see the reflective patch on my Ortlieb Office Pannier bag. I think the reflective patch is brighter.

Biannual mileage total update – #cycling #green #money

I’ve updated my spreadsheet and my biannual cycling mileage totals are:
2008 (H2) = 1600 miles
2009 (H1) = 1100 miles
2009 (H2) = 1400 miles
2010 (H1) = 1500 miles
2010 (H2) = 3500 miles
2011 (H1) = 3700 miles
2011 (H2) = 3600 miles

So I’m doing around 7000 miles per year on my bicycle rather than in a car, saving me a fortune!

See last post about this here

Light snow, to ride or not? #cycling #snow #winter

This morning I looked out onto the back garden and couldn’t even see the mess that I call my lawn, or the path to my “bike” shed

So I went out with a view of assessing whether it was suitable for commuting to work, it didn’t look good to start with

A closer look it wasn’t that deep

The garden dwellers weren’t happy either

Anyway, I decided I’d go for it and I wasn’t on my own either

My road was pretty virgin snow as nobody uses it, I failed to take a photo, I was having too much fun.

Busier side roads were more passable

The main roads were clear more or less and I arrived safely and happy I’d braved it and not resorted to using the car

(shame about that one, the lens must have misted up on my phone)

I’m looking forward to the Friday night ride home now, ride safe, have fun.

Abrasion puncture from damaged tyre #cycling

I’ve had three punctures in short succession from abrasions with the tyre where the surface is damaged (cut, sliced, worn, not sure sure which).
So I was going through these:

Like this:

(I’m proud I did a nice job of that one)
The Park Tool VP-1 Patch Kit does a pretty good job, no chalk or crayon though, so the child in me was disappointed.

I ordered some new tyres and whilst I was waiting for them I stuck patches (pre glued kind) on the abrasions to last me a couple of hundred miles:


I was impressed by the Super Patch kit by Park Tool, very sticky, so care required to not stick it to itself. They really protected the inner tube until my new tyre arrived.
I’m a vulcanising solution man for permanent repairs as the puncture repair at the top shows, but these pre glued ones seemed better fitted to this task (and quicker).

SQL Server: Updating XML fields – XPath, XQuery

Further to my post SQL Server: Querying XML fields – XPath, XQuery, I’d like to expand on this and cover updating XML fields too.

Unfortunately it appears you can only do one update at at a time, so often looping is required, extracting data into temporary tables or complex XPath.
I’m going to cover the later as it’s one that isn’t so well written about.

The great thing about SQL Server is you can use the XPath String Functions, for example I’ve used the concatenation and substring functions before and here is a simple example to show how they can be used. Of note is also the neat way to include SQL variables into the XPath.

-- Create a temporary table for the example, drop if already exists

BEGIN TRY
DROP TABLE #T1
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #T1
(Val XML)

-- Stick in some XML data

INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')
INSERT INTO #T1 (Val) VALUES ('<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>')

-- Update the cycling row using a SQL variable and some functions (concat and substring)

DECLARE @timvar VARCHAR(MAX)
SELECT @timvar = ' is always '

UPDATE #T1 SET Val.modify('declare namespace TIM="http://tim.xsd";
  replace value of (/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1]
  with fn:concat(
    "Everybody knows that ",
    (/TIM:root/TIM:branch/TIM:leaf[@name="displayname"]/text())[1],
    sql:variable("@timvar"),
    fn:substring((/TIM:root/TIM:branch/TIM:leaf[@name="alternativename"]/text())[1], 14, 3))
')
WHERE Val.query('declare namespace TIM="http://tim.xsd";(/TIM:root/TIM:branch[1]/TIM:leaf[@name="displayname"]/text())[1]')
.value('.', 'NVARCHAR(MAX)') = 'cycling'

-- Check the result

SELECT * FROM #T1

And the result is:

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">cycling</leaf>
    <leaf name="alternativename" type="String">Everybody knows that cycling is always fun</leaf>
  </branch>
</root>

<root xmlns="http://tim.xsd">
  <branch>
    <leaf name="displayname" type="String">running</leaf>
    <leaf name="alternativename" type="String">is sometimes fun</leaf>
  </branch>
</root>