Skip to main content

Posts

Showing posts from September, 2012

Common Table Expressions (CTE)

In Stored procedures and in our SQL Statement sometimes we need to use same query again and again. In that case you can use CTE.
USE AdventureWorks2012
GO

--Defining CTE
WITH Empl
AS
(
SELECT * FROM HumanResources.Employee
)

--Using CTE
SELECT Empl.* FROM Empl
GO

And you can use several CTE’s in one statement.

USE AdventureWorks2012 GO

--Defining CTE

WITH Empl AS ( SELECT * FROM HumanResources.Employee ),

Pers AS ( SELECT * FROM Person.Person )

--Using CTE

SELECT Empl.*, Pers.*

FROM Empl INNER JOIN Pers

ON Empl.BusinessEntityID = Pers.BusinessEntityID

GO

Updated : At the very begining I though that CTE were reusable again and again inside a one batch. But it was a myth. I realized it after posting this post, but forgot to remove that today I removed that.

Ranking in T-SQL

In Queries we need to rank and number records. SQL Server gives you few functions to rank your records.RANKRank we can use  to rank data in normal way. In this ranking if we have two 1 next one will have rank 3.  NameMarksRankJohn751Mark751Steve643Gates544USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
[Name] varchar(25),
[Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score])
VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Ranking data over Score
SELECT *, RANK() OVER (ORDER BY [Score] DESC) AS [RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

This ranks based on the column that you specify by OVER.
DENSE_RANK
Dense Rank is not missing ranks like in normal rank function. It has continuity over ranking.



Name
Marks
Dense Rank

John
75
1

Mark
75
1

Steve
64
2

Gates
54
3
USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
[Name] varchar(25),
[Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Scor…

Delete using another Table

In this example I create a temp table on AdventureWorks2012 Database, and first I load all the data in HumanResources.Employee to the temp table. Then I delete data using HumanResources.Employee. This command will delete all the records in Tmp which has Gender = ‘M’ in HumanResources.Employee table.USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT *
INTO Tmp
FROM [HumanResources].[Employee]
GO

--DELETE Using another table
DELETE Tmp
FROM [HumanResources].[Employee] AS HE, Tmp
WHERE Tmp.BusinessEntityID = HE.BusinessEntityID
AND HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

You can use inner or outer join to combine those data.
USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT *
INTO Tmp
FROM [HumanResources].[Employee]
GO

--DELETE Using another table
DELETE Tmp
FROM [HumanResources].[Employee] AS HE
INNER JOIN Tmp
ON…

Updating from other Tables

If you want to synchronize data from another table only for once, you can use this way. When you are going to update from another table don’t forget to give a condition. USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT *
INTO Tmp
FROM [HumanResources].[Employee]
GO

--Retrieving data
SELECT * FROM Tmp

--Update Using another table
UPDATE Tmp
SET Tmp.JobTitle = HE.JobTitle + ', updated'
FROM [HumanResources].[Employee] AS HE
WHERE Tmp.BusinessEntityID = HE.BusinessEntityID


--Retrieving data
SELECT * FROM Tmp
SELECT * FROM HumanResources.Employee
GO

--Dropping Table
DROP TABLE Tmp
GO

If you don’t specify a condition for update it will update all the rows with one value. For example if you run below code you will see the difference.USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT *
INTO Tmp
FROM [HumanResources].[Employee]
GO

--Retrieving data
SELECT * FROM Tmp

--Up…

SELECT INTO

My previous blog post was about using insert in many ways. You can use SELECT INTO to add data from existing table to a new table. In this statement SQL Server will create a table for you. You just want to write the query and add INTO clause to the statement. Rest will done by SQL Server for you.USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee to non existing table
SELECT [BusinessEntityID],
[LoginID],
[OrganizationNode],
[JobTitle],
[BirthDate],
[MaritalStatus],
[Gender]
INTO Tmp
FROM HumanResources.Employee
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

If you are trying to add data into a existing table by using this INTO clause, you will have a error. You can join two table and add data into new table too.USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT HE.[BusinessEntityID],
HE.[LoginID],
HE.[Gender],
PP.[FirstName],
PP.[NameStyle]
INTO Tmp
FROM [HumanResources].[Employee] AS HE
I…

Live Writer and T-SQL

Today I wanted to paste few statements on Live Writer which were coded on SQL Server Management Studio 2012. But when I paste the code into Live Writer I got disappointed about the look. Codes were awesome on SMSS 2012 with colors. Then I wanted to find a way to insert those code with Visual Studio formatting. I found few plugins but they were not cool. Then I found this blog.http://blog.hoegaerden.be/2010/01/15/windows-live-writer-paste-code-plug-in/He had found a nice plugin. I downloaded that and Installed that. Now I use that It is cool plugin. No windows just a plugin.http://plugins.live.com/writer/detail/paste-from-visual-studioHere’s the link for that plugin. Enjoy Blogging!!!

Insert in T-SQL

I’m still a beginner in SQL Server. Then I  will blog about so many basic things in SQL Server. Inserting is very important Data Manipulation Statement. So lets see how to use INSERT Statement in different ways.USE tempdb
GO

--Creating Table
CREATE TABLE tmpTable
(
[id] int NOT NULL,
[name] varchar(100) NULL,
[address] varchar(1000) NULL
)
GO

--Inserting Data
INSERT INTO tmpTable([id],[name],[address]) VALUES(1, 'John', '123, qwerty')
GO

--Inserting Data by Changing order column
INSERT INTO tmpTable([name],[id],[address]) VALUES('Mark',2, '234, qwerty')
GO

--Inserting Data without specifying columns
INSERT INTO tmpTable VALUES(3,'Steve', '454, qwerty')
GO

--Deleting temp table
DROP TABLE tmpTable
GO

Above statements are the basic inserting methods. But its recommended to specify columns. If you want to change the schema structure later It will not affect your code if you have used INSERT with specified columns. And if you don’t specify columns be careful beca…

Sep 01

සැප්තැම්බර් මාසෙත් පටන් ගත්තා. උසස්පෙලත් ඉවර වුනා විතරයි. ඉතින් මට මේ මාසේ පටන් ගත්ත හැටි කියලා වැඩක් නැ. පුදුමාකාර සතුටුටක් එක්ක තමා මේ මාසේ පටන්ගත්තේ. උදේ පාන්දර ඉතින් මගේ BIT ‍ක්ලාස් ගිහින් 1ට විතර බම්බලපිටියේ ඉදන් මම ගියා මරදාන පැත්තට. දවල්ට මරදානේ කඩේකින් වලදලා එහෙම ඉතින් TweetupSL 3 එක තියන Warehouse Project තියන තැනට යන්න ගත්තා. ඇත්තමයි මට හිතුනේ මට පාර වැරදිලා කියලා. ඒත් Warehouse එක ඇතුලට ගියාම තමයි තේරුනේ මට වැරදිලා නෑ. මම ඉන්නේ හරි තැන කියලා. Event එක 3pm පටන් ගන්න තිබුනත් මම එතනට ටිකක් කලින් ගියා. ඉතින් 3 වෙන්න හම්බු‍නේ නෑ කට්ටිය පිරුණා. දන්න කියන අය හිටියා දනේනේ නැති අය හිටියා ගොඩක් අය අදුරගත්තා. අදුරන දැකලා නැති අය බලාගත්තා. පුදුම සතුටකින් එවෙලාවේ හිටියේ.
මගේ බ්ලොග් එකේ හෙඩර් එකේ දාලා තියන පින්තුරේ ගත්තෙත් එහෙදිම තමා. එකේ ෆුල් ක්‍රෙඩිට් එක @sameerawin අයියට යන්න ඕනේ.


Related Links:
http://www.sabithl.blogspot.com/2012/09/tweetupsl-3-sl.html