Feeds:
Posts
Comments

Posts Tagged ‘process’


--Please note section 3 nneds to be run each time an Insert, Update or Delete is performed. 
--If section 3 is not run after each DML then only the last DML can be garaunteed.

/*  Section 1: Run to reset. This recreates the tables and makes it ready from scratch

if object_id('tempdb..##org') is not null drop table ##org

if object_id('tempdb..##hist') is not null drop table ##hist

create table ##org
(
	id int identity 
	, name varchar (50)
	, age int 
)

create table ##hist
(
	sur int identity 
	, id int 
	, name varchar (50)
	, age int 
	, isCurrent bit 
	, EffectiveFrom datetime
	, EffectiveTo datetime
)

insert into ##org values 
	('tom','34')
	, ('dick','29')
	, ('harry','40')

insert into ##hist
select * , 1, GETDATE(), '9999-1-1'
from ##org

select * from ##org 
select * from ##hist

*/


/*		Section 2: Perform an opertion. Perform an Insert, Update or Delete 

-- insert 
insert into ##org values ('ron','19')

-- update 
update ##org set name = 'jerry' where id =1

-- delete 
delete from ##org where id = 3

select * from ##org  
select * from ##hist


*/


--Section 3: Run this each time an above DML is performed and the changes will be saved in the history table (##hist)

insert into ##hist (id, name, age, isCurrent, effectiveFrom, effectiveTo) 
select id, name, age, 0 as  isCurrent, effectiveFrom, effectiveTo
from 
	(
	merge ##hist as target 
		using ##org as source 
			on		source.id = target.id 

	when MATCHED  
				and isCurrent = 1
				and exists -- UPDATE - when record exists but some other fields changed (the bleow also handles nulls)
						(select source.id, source.name, source. age
						except 
						select target.id, target.name, target. age)

	then  -- UPDATE - when record exists but some other fields changed
		UPDATE set
			Target.name = Source.name
			, Target.age = Source.age
			, Target.isCurrent = 1 
			, Target.EffectiveFrom = getdate()
			, Target.EffectiveTo = '9999-1-1' 

	when NOT MATCHED BY TARGET -- INSERT -- new records exits in the source 
	then 
		INSERT --(id, name, age, isCurrent, EffectiveFrom, EffectiveTo) 
		values (source.id, source.name, source.age, 1, getdate(), '9999-1-1')
	
	when NOT MATCHED BY SOURCE -- DELETE -- if a record is not found then UPDATE EffectiveTo date as getdate() adn set isCurrent to 0
	then 
		UPDATE set
			Target.isCurrent = 0
			, Target.EffectiveTo = getdate()
	------------------------------
	-- Getting the previous row before the update is comminted 
	output $ACTION ActioinOut
			,  deleted.id, deleted.name, deleted.age, inserted.isCurrent as IsCurrent, deleted.effectiveFrom, getdate() as effectiveTo
	------------------------------
	) RowBeforeMerge
where RowBeforeMerge.ActioinOut = 'Update'
	and RowBeforeMerge.isCurrent = 1 -- this helps not to write the the row that is deleted twice
									-- when DELETED inserted.isCurrent is equal to 0
	;

select * from ##org  
select * from ##hist



Advertisements

Read Full Post »

You can passs temp table inside a dynamic sql and it will process that for you


declare @s varchar(200)

if object_id('tempdb..#t') is not null 
drop table #t

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)
	'

exec (@s)

select * from #t -- seccond time

 

Read Full Post »

Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts

(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a

cmd extension. Then running this file would execute all the lines in the file.

Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.

Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”

The above if run in commandline will execute the script in the path.

This is usefull when we need to run batches of scripts.

To make scripts upload/commit to a server by using the above command make sure the SP/Function or other

objects are alwasy dropped and recreated e.i. starts with the create statement as below:

--For SP:
---------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[mySP]]
GO

CREATE PROCEDURE [dbo].[mySP] @var AS VARCHAR(100)
as 
begin 
	...
end 


--For Function:
--------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', 

N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[myUDF]
GO


create FUNCTION [dbo].[myUDF]
(
	@var AS VARCHAR(100)
)
RETURNS INT
AS 
BEGIN
	...
end 

Read Full Post »