I always seem to remember the access syntax for this rather than the sql server version–

update
    hachette_digitalideas_import
set
    harrietId = u.Id
from
    hachette_digitalideas_import h
    left outer join user_group u
        ON (h.email = u.email or h.[full name] = u.name) AND u.btype = 'u'

As opposed to the following in access;

update
    hachette_digitalideas_import
    left outer join user_group u
        ON (h.email = u.email or h.[full name] = u.name) AND u.btype = 'u'
set
    harrietId = u.Id

In Summary: I basically wanted to take the data in a table, then linked data from another table concatenated into a comma separated string, and attached as a varchar column on the end of the first table!

I have the mis-fortune of working on a terrible intranet system based on technology built by a company I won’t actually name- so for the purpose of this post, lets say they are called Source. It’s basically a big buggy CMS powered by some aging and creaky classic asp files and lashing of very frail javascript (this isn’t a legacy system- it was installed in the company around a year ago!). It allows you to build forms and data grids, but is quite restrictive with how you attach these to the database- you cannot for example use a stored procedure as a data source- it has to be a table or a view.

We have an internal app to allow staff to order with a table structure like so;

OrderHeader -> OrderDetail -> Publisher -> Warehouse

On the order header view page we need to know which warehouses are involved, which means a query going right down the chain from product on the order line, to the publisher, down to which warehouse that publisher is based at- because we can only tie to a single table or view I had a problem unless i wanted loads of repeated rows- after some googling I found CROSS to be what I was after;

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Based on the above post I was able to build SQL like so;

SELECT
	OrderHeader.id,
	OrderHeader.user_id,
	OrderHeader.OrderDate,
	Users.name,
	WareHouses = LEFT(o.list, LEN(o.list)-1)
FROM
	OrderHeader INNER JOIN
	User ON OrderHeader.UserId = User.UserId LEFT OUTER JOIN
CROSS APPLY
(
	SELECT distinct
		CONVERT(VARCHAR(50), WareHouse.Name) + ',' AS [text()]
	FROM
		WareHouse
		INNER JOIN Publishers ON WareHouse.id = Publishers.WarehouseId
		INNER JOIN OrderDetailLine ON Publishers.PublisherID = OrderDetailLine.PublisherID
	WHERE
		OrderDetailLine.OrderHeaderId = OrderHeader.OrderHeaderId
	ORDER BY
		CONVERT(VARCHAR(50), WareHouse.Name) + ','
	FOR XML PATH('')
) o (list)

This bought me back each row, with a comma separated list of the warehouse names in the end column which i could then filter on!

In SQL Server 2005 onwards you can have conditional Order By clauses in your stored proc’s- for example

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

CASE (Transact-SQL).

The command line syntax for truncating the SQL log’s once they are full!

BACKUP <db_name> LOG  WITH TRUNCATE_ONLY