Thursday, February 18, 2010
Is ISOLATION LEVEL READ UNCOMMITTED same as WITH (NOLOCK)
In one word, YES they are the same.
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
Check here for more detailed information
Also here is another document :
Read Committed Isolation Level
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
Check here for more detailed information
Also here is another document :
Read Committed Isolation Level
Find out all FK CONSTRAINT that exist on a table
declare @tableName varchar(200)
set @tableName = 'ssis_tasks'
select * from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME in
(
select k.name from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
)
set @tableName = 'ssis_tasks'
select * from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME in
(
select k.name from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
)
Thursday, February 11, 2010
Run SP automatically when SQL Server starts
EXECUTE sp_procoption
@procname = 'usp_StartTrace',
@optionname = 'startup',
@optionvalue = 'on'
@procname = 'usp_StartTrace',
@optionname = 'startup',
@optionvalue = 'on'
Identify Primary Keys and PK column name
SELECT KU.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION
Tuesday, February 09, 2010
Change Tracking with SQL 2008
USE [Test]
GO
DROP TABLE [ChangeTrack]
GO
CREATE TABLE [dbo].[ChangeTrack](
[LineID] [int] IDENTITY(1000,1) NOT NULL,
[Fname] [nvarchar](100) NULL,
[Lname] [nvarchar](100) NULL,
[InsertDate] [datetime] NULL,
CONSTRAINT [PK_ChangeTrack] PRIMARY KEY CLUSTERED
(
[LineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeTrack] ADD CONSTRAINT [DF_ChangeTrack_InsertDate] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ChangeTrack] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO
declare @synchronization_version bigint , @last_synchronization_version bigint
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
waitfor delay '00:00:05'
--load table
insert into [ChangeTrack] (Fname, Lname ) values ('Pat','Rick'), ('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick')
waitfor delay '00:00:05'
--Obtain initial data set.
--SELECT * FROM [ChangeTrack]
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1302
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'
--some updates
update [ChangeTrack] set Fname = Fname + '_CHANGED' where LineID % 10 = 4
waitfor delay '00:00:05'
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1312
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'
--some insert/update/delete
insert into [ChangeTrack] (Fname, Lname ) values ('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111')
update [ChangeTrack] set Fname = '123' where LineID % 10 = 5
delete from [ChangeTrack] where LineID % 10 = 6
waitfor delay '00:00:05'
--select * from [ChangeTrack]
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1313
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION()
GO
DROP TABLE [ChangeTrack]
GO
CREATE TABLE [dbo].[ChangeTrack](
[LineID] [int] IDENTITY(1000,1) NOT NULL,
[Fname] [nvarchar](100) NULL,
[Lname] [nvarchar](100) NULL,
[InsertDate] [datetime] NULL,
CONSTRAINT [PK_ChangeTrack] PRIMARY KEY CLUSTERED
(
[LineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeTrack] ADD CONSTRAINT [DF_ChangeTrack_InsertDate] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ChangeTrack] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO
declare @synchronization_version bigint , @last_synchronization_version bigint
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
waitfor delay '00:00:05'
--load table
insert into [ChangeTrack] (Fname, Lname ) values ('Pat','Rick'), ('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick')
waitfor delay '00:00:05'
--Obtain initial data set.
--SELECT * FROM [ChangeTrack]
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1302
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'
--some updates
update [ChangeTrack] set Fname = Fname + '_CHANGED' where LineID % 10 = 4
waitfor delay '00:00:05'
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1312
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'
--some insert/update/delete
insert into [ChangeTrack] (Fname, Lname ) values ('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111')
update [ChangeTrack] set Fname = '123' where LineID % 10 = 5
delete from [ChangeTrack] where LineID % 10 = 6
waitfor delay '00:00:05'
--select * from [ChangeTrack]
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1313
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION()
PIVOT , UNPIVOT
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
select * from pvt
--Unpivot the table.
SELECT VendorID, Employee, Orders
into #tmp
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
select * from #tmp
select * from pvt
-- pivot table
select *
from #tmp
pivot
(
sum(orders)
for employee in (Emp1, Emp2, Emp3, Emp4, Emp5)
) as p
---------------------------
--dynamic creation
declare @str nvarchar(1000)
set @str = ''
select @str = @str + employee+ ',' from #tmp group by employee
set @str = left(@str, LEN(@str)-1)
select @str
declare @sql nvarchar(1000)
set @sql = '
select *
from #tmp
pivot
(
sum(orders)
for employee in ('+@str+')
) as p'
print @sql
exec sp_executesql @sql
---------------------------
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
select * from pvt
--Unpivot the table.
SELECT VendorID, Employee, Orders
into #tmp
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
select * from #tmp
select * from pvt
-- pivot table
select *
from #tmp
pivot
(
sum(orders)
for employee in (Emp1, Emp2, Emp3, Emp4, Emp5)
) as p
---------------------------
--dynamic creation
declare @str nvarchar(1000)
set @str = ''
select @str = @str + employee+ ',' from #tmp group by employee
set @str = left(@str, LEN(@str)-1)
select @str
declare @sql nvarchar(1000)
set @sql = '
select *
from #tmp
pivot
(
sum(orders)
for employee in ('+@str+')
) as p'
print @sql
exec sp_executesql @sql
---------------------------
Monday, February 01, 2010
Data Type GEOMETRY usage to draw my face
SELECT geometry::Point(x, y, 0).STBuffer(0.4) As sig
FROM ( VALUES
(02,16),(02,17),(02,19),(02,20),(02,25),(02,26),(02,27),(02,28),(03,29),(03,30),(02,31),(02,32),(02,33),
(03,15),(03,22),(03,23),(03,24),(03,26),(03,27),(03,28),(03,29),(03,30),(03,31),(03,32),(03,33),(03,34),(03,35),(03,36),
(04,31),(04,32),(04,33),(04,34),(04,35),(04,36),(04,37),
(05,35),(05,36),(05,37),(05,38),(05,39),(05,40),
(06,23),(06,26),(06,37),(06,38),(06,39),(06,40),
(07,23),(07,24),(07,26),(07,38),(07,39),(07,40),(07,41),
(08,16),(08,22),(08,23),(08,24),(08,26),(08,27),(08,40),(08,41),(08,42),
(09,12),(09,13),(09,23),(09,24),(09,26),(09,27),(09,41),
(10,12),(10,13),(10,23),(10,24),(10,25),(10,26),(10,27),(10,43),
(11,13),(11,23),(11,24),(11,25),(11,39),(11,41),
(12,02),(12,13),(12,22),(12,23),(12,25),(12,38),(12,39),(12,40),(12,41),
(13,01),(13,02),(13,10),(13,13),(13,16),(13,17),(13,39),(13,40),(13,41),(13,42),
(14,01),(14,02),(14,07),(14,12),(14,39),(14,40),(14,41),(14,42),
(15,01),(15,07),(15,39),(15,40),(15,41),(15,42),(15,43),
(16,01),(16,15),(16,40),(16,41),(16,42),(16,43),
(17,01),(17,10),(17,16),(17,40),(17,41),(17,42),(17,43),
(18,01),(18,10),(18,13),(18,16),(18,40),(18,41),(18,42),(18,43),
(19,01),(19,10),(19,13),(19,16),(19,23),(19,24),(19,25),(19,39),(19,40),(19,41),(19,42),
(20,01),(20,06),(20,10),(20,11),(20,12),(20,22),(20,23),(20,24),(20,25),(20,26),(20,40),(20,41),(20,42),
(21,02),(21,03),(21,05),(21,06),(21,11),(21,12),(21,22),(21,23),(21,24),(21,25),(21,26),(21,40),(21,41),(21,42),
(22,02),(22,03),(22,12),(22,16),(22,17),(22,22),(22,23),(22,26),(22,27),(22,40),(22,41),
(23,03),(23,04),(23,05),(23,06),(23,12),(23,23),(23,24),(23,27),(23,40),(23,41),
(24,04),(24,05),(24,23),(24,24),
(25,04),(25,05),(25,06),(25,23),(25,24),(25,27),
(26,05),(26,06),(26,07),(26,23),(26,27),
(27,06),(27,07),(27,37),(27,38),(27,39),
(28,07),(28,22),(28,23),(28,24),(28,28),(28,29),(28,30),(28,31),(28,32),(28,35),(28,36),(28,37),(28,38),(28,39),
(29,08),(29,09),(29,24),(29,26),(29,27),(29,28),(29,29),(29,30),(29,31),(29,32),(29,33),(29,34),(29,35),(29,36),(29,37),(29,38),
(30,12),(30,28),(30,29),(30,30),(30,31),(30,32),(30,33),(30,34),(30,35),(30,36),
(31,12),(31,17),(31,18),(31,19),(31,20),(31,21),(31,22),(31,23),(31,24),(31,25),(31,26),(31,27),(31,28),(31,29),(31,30),(31,31),(31,32),(31,33),
(32,21),(32,22),(32,23),(32,24),(32,25),(32,26),(32,27),(32,28)
) AS Coordinates(x, y);
FROM ( VALUES
(02,16),(02,17),(02,19),(02,20),(02,25),(02,26),(02,27),(02,28),(03,29),(03,30),(02,31),(02,32),(02,33),
(03,15),(03,22),(03,23),(03,24),(03,26),(03,27),(03,28),(03,29),(03,30),(03,31),(03,32),(03,33),(03,34),(03,35),(03,36),
(04,31),(04,32),(04,33),(04,34),(04,35),(04,36),(04,37),
(05,35),(05,36),(05,37),(05,38),(05,39),(05,40),
(06,23),(06,26),(06,37),(06,38),(06,39),(06,40),
(07,23),(07,24),(07,26),(07,38),(07,39),(07,40),(07,41),
(08,16),(08,22),(08,23),(08,24),(08,26),(08,27),(08,40),(08,41),(08,42),
(09,12),(09,13),(09,23),(09,24),(09,26),(09,27),(09,41),
(10,12),(10,13),(10,23),(10,24),(10,25),(10,26),(10,27),(10,43),
(11,13),(11,23),(11,24),(11,25),(11,39),(11,41),
(12,02),(12,13),(12,22),(12,23),(12,25),(12,38),(12,39),(12,40),(12,41),
(13,01),(13,02),(13,10),(13,13),(13,16),(13,17),(13,39),(13,40),(13,41),(13,42),
(14,01),(14,02),(14,07),(14,12),(14,39),(14,40),(14,41),(14,42),
(15,01),(15,07),(15,39),(15,40),(15,41),(15,42),(15,43),
(16,01),(16,15),(16,40),(16,41),(16,42),(16,43),
(17,01),(17,10),(17,16),(17,40),(17,41),(17,42),(17,43),
(18,01),(18,10),(18,13),(18,16),(18,40),(18,41),(18,42),(18,43),
(19,01),(19,10),(19,13),(19,16),(19,23),(19,24),(19,25),(19,39),(19,40),(19,41),(19,42),
(20,01),(20,06),(20,10),(20,11),(20,12),(20,22),(20,23),(20,24),(20,25),(20,26),(20,40),(20,41),(20,42),
(21,02),(21,03),(21,05),(21,06),(21,11),(21,12),(21,22),(21,23),(21,24),(21,25),(21,26),(21,40),(21,41),(21,42),
(22,02),(22,03),(22,12),(22,16),(22,17),(22,22),(22,23),(22,26),(22,27),(22,40),(22,41),
(23,03),(23,04),(23,05),(23,06),(23,12),(23,23),(23,24),(23,27),(23,40),(23,41),
(24,04),(24,05),(24,23),(24,24),
(25,04),(25,05),(25,06),(25,23),(25,24),(25,27),
(26,05),(26,06),(26,07),(26,23),(26,27),
(27,06),(27,07),(27,37),(27,38),(27,39),
(28,07),(28,22),(28,23),(28,24),(28,28),(28,29),(28,30),(28,31),(28,32),(28,35),(28,36),(28,37),(28,38),(28,39),
(29,08),(29,09),(29,24),(29,26),(29,27),(29,28),(29,29),(29,30),(29,31),(29,32),(29,33),(29,34),(29,35),(29,36),(29,37),(29,38),
(30,12),(30,28),(30,29),(30,30),(30,31),(30,32),(30,33),(30,34),(30,35),(30,36),
(31,12),(31,17),(31,18),(31,19),(31,20),(31,21),(31,22),(31,23),(31,24),(31,25),(31,26),(31,27),(31,28),(31,29),(31,30),(31,31),(31,32),(31,33),
(32,21),(32,22),(32,23),(32,24),(32,25),(32,26),(32,27),(32,28)
) AS Coordinates(x, y);
Thursday, January 21, 2010
Merge tables Sample
select * from [target_stage]
Merge into [target] as t
using target_stage s on t.lineid = s.lineid
when matched then update
set t.fname = s.fname ,
t.lname = s.lname,
t.[ModifiedTimeStamp] = s.[ModifiedTimeStamp]
when not matched then insert ([LineID],[Fname],[Lname],[InsertDate],[ModifiedTimeStamp])
values (s.[LineID],s.[Fname],s.[Lname],s.[InsertDate],s.[ModifiedTimeStamp])
when not matched by source then delete
output INSERTED.Lineid;
select * from [target]
Merge into [target] as t
using target_stage s on t.lineid = s.lineid
when matched then update
set t.fname = s.fname ,
t.lname = s.lname,
t.[ModifiedTimeStamp] = s.[ModifiedTimeStamp]
when not matched then insert ([LineID],[Fname],[Lname],[InsertDate],[ModifiedTimeStamp])
values (s.[LineID],s.[Fname],s.[Lname],s.[InsertDate],s.[ModifiedTimeStamp])
when not matched by source then delete
output INSERTED.Lineid;
select * from [target]
Subscribe to:
Posts (Atom)