Feeds:
Posts
Comments

Posts Tagged ‘TSQL’

-- Please note that this is not my code and I have found it somewhere on the net

CREATE FUNCTION [dbo].[udf_Split] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(data NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (data)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

Advertisements

Read Full Post »


--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



Read Full Post »

declare @t table&nbsp;( f1 int identity , f2 varchar(100))
insert into @tselect 'hello'unionselect 'world'

select * from @t&nbsp;

-- Table to XML&nbsp;
select&nbsp; ( select 1 as f1r1 , 2 as f1r2 for XML path ('f1'), type ),
 ( select 'hello' as f2r1 , 'world' as f2r2 for XML path ('f2'), type )for XML path (''), root('sample')

-- XML to table&nbsp;declare @myXML xml
set @myXML =&nbsp;'&lt;sample&gt;&nbsp; &lt;f1&gt;&nbsp; &nbsp; &lt;f1r1&gt;1&lt;/f1r1&gt;&nbsp; &nbsp; &lt;f1r2&gt;2&lt;/f1r2&gt;&nbsp; &lt;/f1&gt;&nbsp; &lt;f2&gt;&nbsp; &nbsp; &lt;f2r1&gt;hello&lt;/f2r1&gt;&nbsp; &nbsp; &lt;f2r2&gt;world&lt;/f2r2&gt;&nbsp; &lt;/f2&gt;&lt;/sample&gt;'

select&nbsp; a.b.value('f1[1]/f1r1[1]','int') as f1 , a.b.value('f1[1]/f1r2[1]','int') as f2
 , a.b.value('f2[1]/f2r1[1]','varchar(10)') as f3 , a.b.value('f2[1]/f2r2[1]','varchar(10)') as f4from @myXML.nodes('sample')a(b) --read this as @myXML.nodes('[root]') [table name]([column name])

Read Full Post »

declare @columnList varchar(5000) = ''&nbsp;
-- The will give you a actual name of all the Fields in a table&nbsp;select @columnList = @columnList + case when column_id = 1 then '' else ',' end + '[' + name + ']'from sys.columns cwhere object_id = object_id('schema.aTable')
print 'Field Captions: ' + @columnList

set @columnList = null -- reinitialising.&nbsp;-- The below will give you the a list of all values of a field as a CSV&nbsp;set @columnList = null -- reinitialise&nbsp;SELECT @columnList= isnull(@columnList + ',[' + FieldName + ']' ,'[' + FieldName + ']')FROM aTable
print 'Values of a field: ' + @columnList

Read Full Post »


-- Please note this is not my work.
-- Code cpoied and altered from:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/#

&nbsp;

IF OBJECT_ID ('tempdb..#books') IS NOT NULL
DROP TABLE #books;

CREATE TABLE #books
(
BookID int NOT NULL PRIMARY KEY,
BookTitle nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL
);

&nbsp;

-- declare @InsertOutput1 table variable

DECLARE @InsertOutput1 table
(
BookID int,
BookTitle nvarchar(50),
ModifiedDate datetime
);

-- insert new row into #books table
INSERT INTO #books
OUTPUT INSERTED.*
INTO @InsertOutput1
VALUES(106, 'One Hundred Years of Solitude', GETDATE());

-- view inserted row in #books table
--SELECT * FROM #books;

-- view output row in @InsertOutput1 variable
SELECT 'INSERT', * FROM @InsertOutput1;

&nbsp;

DECLARE @UpdateOutput2 table
(
OldBookID int,
NewBookID int,
BookTitle nvarchar(50),
OldModifiedDate datetime,
NewModifiedDate datetime,
DiffInSeconds int
);

-- update row in #books table
UPDATE #books
SET
BookID = BookID + 1,
ModifiedDate = GETDATE()
OUTPUT
DELETED.BookID,
INSERTED.BookID,
INSERTED.BookTitle,
DELETED.ModifiedDate,
INSERTED.ModifiedDate,
DATEDIFF(ss, DELETED.ModifiedDate, INSERTED.ModifiedDate)
INTO @UpdateOutput2
WHERE BookTitle = 'One Hundred Years of Solitude';

-- view updated row in #books table
--SELECT * FROM #books;

-- view output row in @UpdateOutput2 variable
SELECT 'UPDATE', * FROM @UpdateOutput2;

&nbsp;

DECLARE @DeleteOutput1 table
(
BookID int,
BookTitle nvarchar(50),
ModifiedDate datetime
);

-- delete row in #books table
DELETE #books
OUTPUT DELETED.*
INTO @DeleteOutput1
WHERE BookID = 107;

-- view updated row in #books table
-- SELECT * FROM #books;

-- view output row in @DeleteOutput1 variable
SELECT 'DELETE', * FROM @DeleteOutput1;

Read Full Post »


--SSIS > Script Task > Message Box
------------------------------------
            string strMessage = Dts.Variables["User::strAlterCmd"].Value.ToString();
            MessageBox.Show(strMessage);

--ALTER TABLE CHECK
-----------------------

IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID

('dbo.usama_mdlDataLake_Behaviour')
				AND name in ('carBreakdown'))		-- UA:You will either 

have all or none so just check any one column

--Check if Index exists / Create
-------------------------------
CONSTRAINT PK_tbl_ComplaintExtracts_LoadID PRIMARY KEY CLUSTERED (LoadID)

IF NOT EXISTS (SELECT top 1 *  FROM sys.indexes  WHERE name = 'ix_churn1')

--TempDB:
	IF NOT EXISTS (SELECT top 1 * FROM tempdb.sys.indexes  WHERE name = 'ix_ChurnIndex1') 

create unique clustered index ix_ChurnIndex1 on #t1(customerid)

CREATE NONCLUSTERED INDEX [IX_tbl_BGL_Transactions_FileName] ON [dbo].[tbl_BGL_Transactions] 

([PolicyNumber])

 IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name'
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
DROP INDEX ix_tblSVV_Permissions_Orchid_CustomerID ON [dbo].[tblSVV_Permissions]

--Table Exists
--------------
IF NOT EXISTS (SELECT top 10 * FROM INFORMATION_SCHEMA.TABLES
   	WHERE TABLE_NAME = 

'DataLoad' -- do not include square brackets
			AND TABLE_SCHEMA = 'stg')
BEGIN       --
  PRINT 'Table Exists'
END

--VIEW Exists
------------------
IF EXISTS(select top 1 * FROM sys.views where name = 'myV')
DROP VIEW myV

--Drop all temp tables
--------------------------

DECLARE @DropTableSQL varchar(max)
SELECT @DropTableSQL = ISNULL(@DropTableSQL + ';', '') + 'drop table ' + SUBSTRING(t.name, 1, 

CHARINDEX('___', t.name) - 1)
FROM tempdb..sysobjects AS t
WHERE t.name LIKE '#%[_][_][_]%'
	AND t.id = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1));
PRINT @DropTableSQL
--EXEC (@DropTableSQL)


--Try catch block
----------------------
BEGIN TRY
DECLARE @MyInt INT, @flag bit = 1  

-- the error state is custom number given so that a developer can locate where the error occured
--http://dba.stackexchange.com/questions/35893/what-is-error-state-in-sql-server-and-how-it-can-be-used

	if @flag = 0
		RAISERROR ('my custom error message', 16, 1); -- the error state a number 
	else 
		SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT @flag flag
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH;
GO 


Read Full Post »


-- List of days in the week
;WITH WeekNames
AS
(
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate

    UNION ALL 

    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
)
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
as
(
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
)
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
as
(
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
)
SELECT YearNumber
FROM Last10Years
ORDER BY YearNumber DESC 





Read Full Post »

Older Posts »