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 :