T-SQL Queries (SQL Server)

XML columns/variables

2012-01-06T01:51:00.001-08:00

CREATE TABLE [dbo].[SampleXmlTable](
[GroupID] [int] NULL,
[Books] [xml] NULL
) ON [PRIMARY]

--select a node

SELECT
GroupID,
Books.value('(catalog/book/author)[1]','nvarchar(200)') as Author,
Books.value('(catalog/book/price)[2]','nvarchar(200)') as Price
FROM
[dbo].[SampleXmlTable]

GroupID Author Price
1 Gambardella, Matthew 5.95
1 Kress, Peter 36.95

--expand

declare @SampleXml Xml

SELECT
DataTable.books.value('author[1]','nvarchar(100)') AS author,
DataTable.books.value('title[1]','nvarchar(100)') AS title,
DataTable.books.value('price[1]','float') AS price
FROM
@SampleXml.nodes('//catalog/book') AS DataTable(books)

Send Mails Using SQL

2012-01-06T00:59:00.000-08:00

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test_Profile', -- should create
@recipients = indikask@gmail.com,
@copy_recipients = indikask2@gmail.com ,
@body = 'test mail from blogg',
@subject = 'test'
--@file_attachments =

MERGE Sample

2012-01-06T00:45:00.000-08:00

CREATE TABLE [dbo].[source](
[id] [int] NULL,
[name] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[source] ([id], [name]) VALUES (5, N'indika')
INSERT [dbo].[source] ([id], [name]) VALUES (6, N'kannangara')
INSERT [dbo].[source] ([id], [name]) VALUES (2, N'saminda')

CREATE TABLE [dbo].[target](
[id] [int] NULL,
[name] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[target] ([id], [name]) VALUES (2, N'saminda')
INSERT [dbo].[target] ([id], [name]) VALUES (3, N'sunny')
INSERT [dbo].[target] ([id], [name]) VALUES (6, N'kanna')

SELECT * FROM [dbo].[source]

id name
5 indika
6 kannangara
2 saminda

SELECT * FROM [dbo].[target]

id name
2 saminda
3 sunny
6 kanna

MERGE INTO dbo.target T
USING (
SELECT [id]
,[name]
FROM [test].[dbo].[source]) S
ON (S.[id]=T.[id] )
WHEN MATCHED
AND NOT S.[name] = T.[name]
THEN UPDATE SET
[name] = S.[name]
WHEN NOT MATCHED THEN
INSERT (
[id]
,[name])
VALUES (
S.[id]
,S.[name])
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

SELECT * FROM [dbo].[target]

id name
2 saminda
6 kannangara
5 indika

Rows into Columns-pivot/unpivot

2011-12-27T02:17:00.001-08:00

PIVOT

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('SPIKE', 'MON', 300)
insert into DailyIncome values ('FREDS', 'SUN', 400)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'TUE', 200)
insert into DailyIncome values ('JOHNS', 'WED', 900)
insert into DailyIncome values ('SPIKE', 'FRI', 100)
insert into DailyIncome values ('JOHNS', 'MON', 300)
insert into DailyIncome values ('SPIKE', 'SUN', 400)
insert into DailyIncome values ('JOHNS', 'FRI', 300)
insert into DailyIncome values ('FREDS', 'TUE', 500)
insert into DailyIncome values ('FREDS', 'TUE', 200)
insert into DailyIncome values ('SPIKE', 'MON', 900)
insert into DailyIncome values ('FREDS', 'FRI', 900)
insert into DailyIncome values ('FREDS', 'MON', 500)
insert into DailyIncome values ('JOHNS', 'SUN', 600)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('SPIKE', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'FRI', 300)
insert into DailyIncome values ('JOHNS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'SAT', 800)
insert into DailyIncome values ('SPIKE', 'TUE', 100)
insert into DailyIncome values ('SPIKE', 'THU', 300)
insert into DailyIncome values ('FREDS', 'WED', 500)
insert into DailyIncome values ('SPIKE', 'SAT', 100)
insert into DailyIncome values ('FREDS', 'SAT', 500)
insert into DailyIncome values ('FREDS', 'THU', 800)
insert into DailyIncome values ('JOHNS', 'TUE', 600)

select * from DailyIncome
pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay


UNPIVOT


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
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Get host Name/Server name

2011-12-27T01:01:00.000-08:00

select @@servername
or
select SERVERPROPERTY ('ServerName')

Create Database

2011-12-27T00:14:00.000-08:00

Syntax

CREATE DATABASE database_name
[ ON
{ [ PRIMARY ] [ [ ,...n ]
[ , [ ,...n ] ]
[ LOG ON { [ ,...n ] } ] }
]
[ COLLATE collation_name ]
[ WITH ]
]
[;]

Adding Arguments
There are a number of optional arguments that you can supply with the CREATE DATABASE command. You should check your database system's documentation for the specific arguments supported and their usage, but here's an example of supplying arguments when creating a database using Microsoft's SQL Server.

more>>

Example Code
In this example, we are supplying the name and location of the database's data file and transaction log. We are also specifying the initial size of these files (with the SIZE argument), the maximum size it can grow to (with the MAXSIZE argument) and the growth increment of each file (using the FILEGROWTH) argument.

USE master
GO
CREATE DATABASE testDB
ON
( NAME = testDB_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\testDBdat.mdf',
SIZE = 20MB,
MAXSIZE = 70MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'testDB_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\testDB.ldf',
SIZE = 10MB,
MAXSIZE = 40MB,
FILEGROWTH = 5MB )
GO