August 25, 2014

How to import data into SQL Server using TSQL

There are features such as Import/Export and SQL Server Integration services to get the data into SQL Server. We can get the data directly into SQL Server using SQL command such as
BCP, BULK INSERT, OPENROWSET, OPENDATASOURCE, OPENQUERY and LINKED SERVERS. 

August 20, 2014

SQL - Case Statement

Case statement is used as conditional operator in Structure Query Language which compares the expression and returns the possible results. Case statement can also be used to evaluate a set of Boolean expression. Most often Case statement is useful in stored procedures, views and formula for a particular column while designing the table.

Syntax:

CASE expression
         WHEN expression1 THEN expression1
         WHEN expression2 THEN expression2
          .....
ELSE expressionN
END

--------------------------------------------------------------------------------------------------------------------------

For example:

DECLARE @Val INT = 1

SELECT
CASE @Val
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Not Known'
END

Result: First
--------------------------------------------------------------------------------------------------------------------------

DECLARE @Val INT  = 5

SELECT
CASE
WHEN @Val < 0 THEN 'Negative'
WHEN @Val < 3 THEN 'Less than 3'
WHEN @Val > 3 THEN 'Greater than 3'
ELSE 'Value not assigned'
END

Result: Greater than 3

--------------------------------------------------------------------------------------------------------------------------

Formula for a particular column while designing the table.

Below, you can see on the formula
(case [RoleID] when (1) then '100' when (2) then '75' when (3) then '50' else '0' end)


Result:



When Role is assigned to the Sales man then it will automatically set the value on the SalesTarget column based on the RoleID assigned.

August 8, 2014

SQL Server - Split the string and insert into table

This script using common table expression below will help to split the given string and insert into the data table.

DECLARE @TempTable TABLE(Name varchar(50))

DECLARE @String VARCHAR(255) = 'Kale,Gore,Nepte,Chepte'

;WITH CTE AS
(
    SELECT LEFT(@String, CHARINDEX(',', @String) -1) AS Name, RIGHT(@String, LEN(@String) - CHARINDEX(',', @String)) AS RemainingString
    UNION ALL
    SELECT LEFT(RemainingString, CHARINDEX(',', RemainingString) -1) AS Name, RIGHT(RemainingString, LEN(RemainingString) - CHARINDEX(',', RemainingString)) AS RemainingString
    FROM CTE
    WHERE CHARINDEX(',', RemainingString)>0
    UNION ALL
    SELECT RemainingString AS Name, NULL AS Remainder
    FROM CTE
    WHERE CHARINDEX(',', RemainingString)=0
)

Insert into @TempTable (Name) SELECT Name FROM CTE

SELECT * FROM @TempTable