Joel Greijer

/* Short question: is "where not exists" atomic? Short answer: No it will fail, if not in transaction or using locking hint. Even merge will fail. Follow steps to reproduce below. (Written on sql2008r2) */   /* Preparation script */   /* drop table dbo.Monkey drop table stage.Monkey */   create   table   dbo . Monkey   (  MonkeyId   int ,     MonkeyDate   datetime ,     primary   key   clustered   ( Monkeyid ) ) create   table   stage . Monkey   (  MonkeyId   int   identity ( 1 , 1 ) ,     MonkeyDate   datetime ) go set   nocount   on insert   stage . Monkey   select   getdate ( ) go  10 insert   stage . Monkey   select   getdate ( )   from   stage ...

/* This script-generating script will output t-sql script for comparing data in tables for two databases. Some columns with special data types are excluded. Maybe they can be casted to varchar, but its not done here. Tip: Execute this script in the database with least tables. Remember to run output script in both directions since it uses "except" (switch db1 db2). Will report diff row count and rows themselves. Can be heavy if big db.   Use "Copy with headers" in result below for correct script Written on MsSql 2008r2 */ declare  @db1 varchar(100) = 'Db1', @db2 varchar(100) = 'Db2' declare @excludedTypes as table (TypeName varchar(256))declare @excludedColumns as table (ColumnName varchar(256)) insert @excludedTypes values ('image'),('text'),('ntext'),('xml'),('timestamp') insert @excludedColumns values ('ColumnToIgnore1'),('ColumnToIgnore2') select distinct   'select '+ ...

Ignore namespaces! "select @xmlDoc.query('data(/*:authors/*:child/*:id)') as UserID" from blog post  http://maxbarrass.blogspot.se/2008/02/sql-2005-xml-xquery-ignore-namespaces.html