INSERT INTO statement

The INSERT statement adds one or more new rows to a table. Use a SELECT subquery to specify the data values for one or more rows.

Syntax :
INSERT INTO [existing table] ([column])
SELECT [column name]
FROM [table name]



Example :
INSERT INTO dbo.Shop (ShopNo,ProductNo,Stock)
SELECT ForeignKey, Description
FROM dbo.NewShop

SELECT INTO statement

The SELECT INTO statement creates the new table by copying schema and other information that is not specific to the actual algorithm.

Syntax :
SELECT [column]INTO [new table]FROM [existing table]

Example :
SELECT * INTO dbo.NewShop FROM dbo.Shop

Crosstab Query

Crosstab query :

A cross tab query is a transformation of rows of data to columns.It is easier review any data from rows.

Two examples of a cross-tab is to take the above data and show how much your shops stocked in each product across. This allows for easy comparison of stock by product.

This is source data from dbo.Shop's table.

Example 1 :

Move data in column of ProductNo to column of MLR,MLB and MLC.

SELECT ShopNo,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLR') AS MLR,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLB') AS MLB,
(SELECT Stock FROM dbo.Shop WHERE ShopNo = S.ShopNo AND ProductNo = 'MLC') AS MLC
FROM dbo.Shop S GROUP BY ShopNo

Result :

Example 2 :

This is example of summary stock by product.

SELECT ShopNo,

SUM(CASE WHEN ProductNo ='MLR' THEN Stock ELSE 0 END) AS MLR,

SUM(CASE WHEN ProductNo ='MLB' THEN Stock ELSE 0 END) AS MLB,

SUM(CASE WHEN ProductNo ='MLC' THEN Stock ELSE 0 END) AS MLC,

SUM(Stock) AS Total

FROM Shop

GROUP BY ShopNo

Result :

IsZero

When you divide the numeric by zero,you'll get the error message.

Message errorServer: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

You can use this function to solve this problem.

Create functon :

CREATE FUNCTION dbo.iszero(@Value numeric,@Replace int)
RETURNS int
AS
BEGIN
DECLARE @Result int
IF @Value = 0
SET @Result = @Replace
ELSE
SET @Result = @Value
RETURN @Result
END

Test : shows null if function returns null value.

SELECT
Id AS ID,
[IND Volume],
[Volume Grade],
[Volume Grade] / dbo.iszero([IND Volume],null) AS DIV_Result
FROM dbo.LampCustomerAll
WHERE (ASM_Code = 'LM5000') AND ([IND Volume] = 0) OR ([IND Volume] = 2)
ORDER BY Id DESC

Result :

How to create function

How to create function

CREATE FUNCTION owner_name.function_name ( @parameter_name AS data_type ,...n )
RETURNS data_type
AS
BEGIN
function_body
RETURN value
END


Examples :

CREATE FUNCTION dbo.GetNoWeek (@var Varchar(10))
RETURNS varchar(10)
BEGIN
DECLARE @result varchar(10)
IF @var = 'Ok'
SET @Result = 'Ok'
ELSE
SET @Result = 'Not Ok'
RETURN @Result
END

AddZeroDigit

How to add Zero to an integer value from left (like 9 to 009)

Create function :

CREATE FUNCTION dbo.AddZeroDigit(@Value AS int)
RETURNS varchar(3)

BEGIN

DECLARE @Result varchar(3)

SET @Result = RIGHT('000'+CAST(@Value AS varchar(3)),3)

RETURN @ResultEND

Test :

SELECT dbo.AddZeroDigit(9) AS Result

Result :

DayNameOfWeek

How to get the day name of week .

Create function :

CREATE FUNCTION dbo.DayOfWeek(@Date datetime)
RETURNS varchar(10)
BEGINDECLARE @Today intDECLARE @Result varchar(10)
SET @Today = DATEPART(dw, @Date)

IF @Today = 1 SET @Result = 'SUN'
ELSE IF @Today = 2 SET @Result = 'MON'
ELSE IF @Today = 3 SET @Result = 'TUE'
ELSE IF @Today = 4 SET @Result = 'WED'
ELSE IF @Today = 5 SET @Result = 'THU'
ELSE IF @Today = 6 SET @Result = 'FRI'
ELSE IF @Today = 7 SET @Result = 'SAT'
RETURN @ResultEND

Test :

SET DATEFIRST 7
select dbo.DayOfWeek('2007-09-05') as ToDay

Result :

DateNextWeek

How to find the date of Monday or Friday in next week .

Create Function :

CREATE FUNCTION dbo.NextWeek (@toDay datetime,@typeOutput int)
RETURNS datetime
BEGIN DECLARE @noDay int
DECLARE @answer datetime
DECLARE @startDayNextWeek datetime
DECLARE @endDayNextWeek datetime
SET @noDay=DATEPART(dw, @toDay)-1
SET @startDayNextWeek =DATEADD(day,8-@noDay,@toDay)
SET @endDayNextWeek =DATEADD(day,12-@noDay,@toDay)
IF(@typeOutput=1)
SET @answer=@startDayNextWeek --return first day in next week
ELSE SET @answer=@endDayNextWeek --return last day in next week
RETURN (@answer)END

Test :

select dbo.NextWeek ('2007-01-01',2)

Result :

NumberOfWeek

How to find number of week in any year when we know the date.

Create Function :

CREATE FUNCTION dbo.GetNoWeek (@startDate datetime)
RETURNS varchar(10)
BEGIN
DECLARE @answer int
DECLARE @theMonth int
DECLARE @theYear int
DECLARE @firstDate as varchar(10)
SET @theMonth = Month(@StartDate)
SET @theYear = Year(@StartDate)
SET @firstDate = CAST( @theYear as varchar(4))+'-'+CAST(@theMonth as varchar(2))+'-'+'01'
SET @answer =dbo.NoOfDayWeek (CAST(@firstDate as datetime),@startDate,'w')
RETURN (@answer)
END

Test :

select dbo.GetNoWeek('2007-01-31')


Result :

FindDate

What is the date? When you set start date,a number of day and a number week.

Create Function :

CREATE FUNCTION dbo.FindDate (@startDate datetime,@day int,@week int)

RETURNS datetime

BEGIN

DECLARE @answer datetime

DECLARE @dateResult datetime

DECLARE @noDayDateStart int

SET @noDayDateStart=DATEPART(dw, @startDate)-1

SET @dateResult =DATEADD(day,( 7*@week)-(7-@day)-@noDayDateStart,@startDate">7 * @week)-(7 - @day) - @noDayDateStart, @startDate)

SET @answer=@dateResult RETURN (@answer)

END

Test :

select dbo.FindDate ('2007-01-01',1,6)

Result :

RoundDown

How to round down any number.

Test :

SELECT ROUND(150.99, 0, 1)

Result :

RoundUP

How to round up any number.


Create function :

CREATE FUNCTION dbo.RoundUP(@Value float )RETURNS int AS
BEGIN
DECLARE @Result int
SELECT @Result = ROUND(@Value,0)
IF @Result < @Value SET @Result = @Result + 1 RETURN @Result END Test :

select dbo.RoundUp(124.000001) as result

Result :