Using of BULK INSERT in Sql Server

Using of BULK INSERT 

STEP 1: Create a text file the name STUDENTS_LIST.txt and add the bellow data

1,RAJIV,M.S
2,VINAY KUMAR,B.A
3,HANU,M.B.A
4,ARUN,M.Sc

STEP 2: Create a table using bellow statment

CREATE TABLE #STUDENT(ID INT, [NAME] NVARCHAR(50), EDUCATION NVARCHAR(20))

STEP 3: Now use the Bulk Insert Statement

BULK INSERT #STUDENT

FROM 'D:\STUDENTS_LIST.txt'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

STEP 4: Check the table using bellow query

SELECT * FROM #STUDENT

DROP TABLE #STUDENT

Tags: BULK INSERT, Insert, Sql Server, SQL, Query

Sql Server: CTAS Create table as select

Creating table from another data copy only the snytax but not the data

SELECT * FROM Customers

SELECT * INTO ImpCustomers FROM Customers WHERE 1=2

SELECT * FROM ImpCustomers

O/p:

DROP TABLE ImpCustomers 

Tags: Sql Server: CTAS Create table as select,Create, Copy

Getting the Rows from DB in page wise using Row_Number in SQL Server

Create table #States(StateID INT,StateName NVARCHAR(20))
INSERT INTO #States
SELECT 1,'California' UNION
SELECT 2,'Albama' UNION
SELECT 3,'Alaska' UNION
SELECT 4,'Arizona' UNION
SELECT 5,'Florida' UNION
SELECT 6,'Hawaii' UNION
SELECT 7,'Montana' UNION
SELECT 8,'Indiana' UNION
SELECT 9,'Lowa' UNION
SELECT 10,'New York' UNION
SELECT 11,'New Mexico' UNION
SELECT 12,'Texas' UNION
SELECT 13,'Virginia' UNION
SELECT 14,'South Carolina' UNION
SELECT 15,'Washington'


SELECT * FROM #States
  

Create the Procedure like

Create Proc GetRecords(@pageNo int=1,@pageSize int)
as begin
Select StateID,StateName from
(Select Row_Number() over (Order By StateID) SNO,StateID,StateName from #States)S
Where S.SNO Between ((@pageNo-1)*@pageSize)+1 and @pageNo*@pageSize
end

Execution:-

exec GetRecords 1,4        --1 is page number & 4 is page size
exec GetRecords 2,4
exec GetRecords 3,4
exec GetRecords 4,4

o/p:

DROP TABLE #States

Tag: Using Row_Number in SQL Server finding records

Switching Rows as columns in SQL Server

CREATE A TABLE CALLED CAR 

CREATE TABLE CAR(ID INT,NAME NVARCHAR(20),COLOR NVARCHAR(20));

INSERT INTO CAR
    SELECT 1,'TATA SUMO','RED' UNION
    SELECT 2,'TATA SUMO','BLUE' UNION
    SELECT 3,'TATA SUMO','WHITE' UNION
    SELECT 4,'TATA SUMO','SILVER' UNION
    SELECT 6,'MARUTHI','BLUE' UNION
    SELECT 7,'MARUTHI','WHITE' UNION
    SELECT 5,'MARUTHI','RED' UNION
    SELECT 8,'MARUTHI','SILVER' UNION
    SELECT 9,'SAFARI','WHITE' UNION
    SELECT 10,'SAFARI','SILVER' UNION
    SELECT 11,'TATA SUMO','RED' UNION
    SELECT 12,'TATA SUMO','SILVER'

SELECT * FROM CAR

SELECT * FROM (SELECT * FROM CAR) p
    PIVOT (count(ID) FOR COLOR IN (RED,BLUE,WHITE,SILVER)) AS pvt
Order by NAME DESC

SELECT * FROM (SELECT * FROM CAR) p
    PIVOT (count(ID) FOR NAME IN ("TATA SUMO",SAFARI,MARUTHI)) AS pvt

O/P:

TAGS: Switching rows as columns in sqlserver, using pivot in sql server, sql pivot

Using of User defined data types in sql server 2008 example

1) Create Type ename FROM nvarchar(20) not null
Create a type called called ename which has nvarchar(20) and not allowing nulls
 
2) Create table tab(id int,name ename)
Create and insert data into the new table 
insert into tab(id,name) 
 Select 1,'asdf' Union all
 Select 2,'badsf' Union all
 Select 3,'ertvfc'

3) Select * from tab

4) Drop table mytab; Drop Type MYTYPE 

Tags:User-Defined Table Types,Types,Types in sql server,sql server 2008 types

Using of User-Defined Table Types in sql server 2008 example

Create the database and do the following

Create database MAKHAM;--Create database with name MAKHAM
Use MAKHAM;
Create Table Emp(EmpID int,EmpName nvarchar(50),Sal float);--Create a table Emp
Create Type EmpType as Table(_EmpID int,_EmpName nvarchar(50),_Sal float);--Create table type EmpType
Create Proc InsEmps(@Emps EmpType ReadOnly) -- Create a procedure to insert bulk records at a time
as 
Begin
   Insert Into Emp(EmpID,EmpName,Sal)
 Select _EmpID,_EmpName,_Sal from @Emps
End
Create Proc UpdEmps(@Emps EmpType ReadOnly) -- Create a procedure to update bulk records at a time
as 
Begin
   Update e Set e.EmpName=_EmpName,e.Sal=_Sal
 From Emp e inner join @Emps es on e.EmpID=es._EmpID 
End  

Create xml file with the name emps.xml

<?xml version="1.0" encoding="utf-8" ?>
<employees>
  <emp><empid>1</empid><empname>Raju</empname><sal>2473</sal></emp>
  <emp><empid>2</empid><empname>Hanu</empname><sal>653</sal></emp>
  <emp><empid>3</empid><empname>Vinay</empname><sal>4567</sal></emp>
  <emp><empid>4</empid><empname>Arun</empname><sal>2345</sal></emp>
  <emp><empid>5</empid><empname>Suman</empname><sal>567</sal></emp>
  <emp><empid>6</empid><empname>Kishore</empname><sal>234</sal></emp>
  <emp><empid>7</empid><empname>Kesav</empname><sal>2566</sal></emp>
</employees>

Create a page and use the code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        InsertData();
    
}

public void InsertData()
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("emps.xml"));
    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=MAKHAM;Persist Security Info=True;User ID=sa;Password=sa;Pooling=False");
    SqlCommand cmd = new SqlCommand("InsEmps", con);
    con.Open();
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sp = new SqlParameter("@Emps", ds.Tables[0]);
    cmd.Parameters.Add(sp);
    cmd.ExecuteNonQuery(); //See the Emp table in db
}

Select * from Emp;
EmpID  EmpName  Sal 
-----  -------  ------
1      Raju     2473 
2      Hanu     653 
3      Vinay    4567 
4      Arun     2345 
5      Suman    567 
6      Kishore  234 
7      Kesav    2566

Now update the xml file like this
  
<?xml version="1.0" encoding="utf-8" ?>
<employees>
  <emp><empid>1</empid><empname>Raju</empname><sal>24373</sal></emp>
  <emp><empid>2</empid><empname>Hanu</empname><sal>6523</sal></emp>
  <emp><empid>3</empid><empname>Vinay</empname><sal>41567</sal></emp>
  <emp><empid>4</empid><empname>Arun</empname><sal>23345</sal></emp>
  <emp><empid>5</empid><empname>Suman</empname><sal>5617</sal></emp>
  <emp><empid>6</empid><empname>Kishore</empname><sal>27234</sal></emp>
  <emp><empid>7</empid><empname>Kesav</empname><sal>25696</sal></emp>
</employees>

and use the methodand use the method

public void UpdateData()
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("emps.xml"));
    SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=MAKHAM;Persist Security Info=True;User ID=sa;Password=sa;Pooling=False");
    SqlCommand cmd = new SqlCommand("UpdEmps", con);
    con.Open();
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sp = new SqlParameter("@Emps", ds.Tables[0]);
    cmd.Parameters.Add(sp);
    cmd.ExecuteNonQuery(); //See the Emp table in db
}

O/P:
Select * from Emp;
EmpID  EmpName  Sal 
-----  -------  ------
1      Raju     24373 
2      Hanu     6523 
3      Vinay    41567 
4      Arun     23345 
5      Suman    5617 
6      Kishore  2734 
7      Kesav    25696



Tags:User-Defined Table Types,Types,Types in sql server,sql server 2008 types

Inserting single quote i.e., ' into a column of sql server table

Declare @TempTable Table(ID int,[Name] nvarchar(20));

Insert into @TempTable values(1,'Vinay''s Computer');

Insert into @TempTable values(2,'Arun''s sister');

Insert into @TempTable values(3,'Radha''s pen');

Select * from @TempTable;

Out put:

ID      Name
--      ----------------
1 Vinay's Computer
2 Arun's sister
3 Radha's pen

Tag: 2005, insert, into, quote, single, single quotes, sql, Sql server, statement, table, temporary table

How to Import data from Sql Server to XLS document

1. Add Reference called Microsoft Excel 12.0 Object Library to your project.

2. Add a web page under it

using System;

using System.Configuration;

using System.Data;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using Excel = Microsoft.Office.Interop.Excel;

public partial class XLSConvert : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            using (SqlConnection con = new SqlConnection(@"Data Source=sqlexpres;Initial Catalog=test;User ID=sa;Password=sa"))

            {

                SqlCommand cmd = new SqlCommand("select * from Country_Tbl;select * from test.dbo.State_Tbl;", con);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();

                da.Fill(ds);

                if (ds.Tables.Count > 0)

                {

                    PrepareExcelSheet(ds);

                    ScriptManager.RegisterStartupScript(this, GetType(), "", 

                        "alert('Excel file created, you can find the file @ c:\\DB_Tables.xls');", true);

                }

            }

        }

    }

    public void PrepareExcelSheet(DataSet ds)

    {

        object misedValue = System.Reflection.Missing.Value;

        Excel.Application xlsApp = new Excel.ApplicationClass();

        Excel.Workbook xlsWorkBook = xlsApp.Workbooks.Add(misedValue);

        for (int k = 0; k < ds.Tables.Count; k++)

        {

            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlsWorkBook.Worksheets.get_Item(k+1);

            xlWorkSheet.Name = ds.Tables[k].TableName.ToString();

            for (int i = 1; i <= ds.Tables[k].Columns.Count; i++)

                xlWorkSheet.Cells[1, i] = ds.Tables[k].Columns[i - 1].ColumnName.ToString();

            for (int i = 1; i <= ds.Tables[k].Rows.Count; i++)

                for (int j = 1; j <= ds.Tables[k].Columns.Count; j++)

                    xlWorkSheet.Cells[i + 1, j] = ds.Tables[k].Rows[i - 1][j - 1].ToString();

            releaseObject(xlWorkSheet);

        }

        xlsWorkBook.SaveAs("DB_Tables.xls", Excel.XlFileFormat.xlWorkbookNormal, misedValue, misedValue, misedValue, misedValue,

            Excel.XlSaveAsAccessMode.xlExclusive, misedValue, misedValue, misedValue, misedValue, misedValue);

        xlsWorkBook.Close(true, misedValue, misedValue);

        xlsApp.Quit();

        releaseObject(xlsWorkBook);

        releaseObject(xlsApp);

    }

    private void releaseObject(object obj)

    {

        try

        {

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

            obj = null;

        }

        catch (Exception ex) { obj = null; }

        finally { GC.Collect(); }

    }

}

Out Put:

Tag: convert, import data, Sql server, xls

Select many rows as one row in SQL

In some situations we may need the data in another table should get as a single row in that situations we can follow like this....

Select * From Customers 

Select * From Hobbies

Select * From CustomerHobbies

Select CustomerID,CustomerName,'Features'=

(Select HobbyDesc+', ' From Hobbies Where HobbyID in 

(Select HobbyID From CustomerHobbies Where CustomerID=Customers.CustomerID)

For XML PATH(''))  From Customers 

Tag: convert, many rows, one row, path, select, single, single row, Sql server, xml

Fetch records from current years first date to current date in sql server

Select dateofPurchase from purchase_tbl where dateofPurchase
Between convert(datetime,convert(nvarchar,datepart(year,GetDate()))+'-1-1') and GetDate()


Tag: Between, Between two dates, Current Year, First Day, Last Day, Sql server

Find First and Last Day of Current Year in Sql Server

select convert(datetime,convert(nvarchar,datepart(year,getdate()))+'-1-1') as 'Current Year First Date',
convert(datetime,convert(nvarchar,datepart(year,getdate()))+'-12-31') as 'Current Year Last Date'

Tag: Current Year, First Day, Last Day, Sql server

How to Get 21st date of the any month in sql server

SELECT DATEADD(d,
case when(datepart(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))=31) then -11
when(datepart(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))=30) then -10
when(datepart(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))=29) then -9
else -8 end,
DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))


Tag: 21st date of any month in sql, 21st date of any month in sql server, last date of any month in sql server, last date of month in sql, last date of month in sql server

Incrementing the primary key string value in sql server

suppose think like you primary key column values like
RA00006701 -- --
RA00006702 -- --
RA00006703 -- --
RA00006704 -- --

In order to get the max(id) as RA00006705  do the following
Declare @maxid nvarchar(15)
select @maxid=substring(max(id),1,2)+RIGHT('00000000'+convert(nvarchar,ISNULL((MAX(Convert(int,substring(id,3,8)))),0)+ 1),8) from tbl_yourtable;
select @maxid

Tag: Incrementing the primary key string value in sql server

using case and between in where clause of sql server

Assume in database table called tbl_HouseDetails contains the columns details as follows
house_id house_owner_id room otherdeatils
int(pk) int int nvarchar(10)
1 2 1 own
2 1 2 own
3 3 2 own
4 2 0 own
5 4 0 own
6 5 3 own
7 2 2 own
8 9 1 own
9 1 1 own
10 3 0 own

If you want to get the house details based on min_room and max_room then use this stored procedure
if you pass minroom as -1 or maxroom as -1 then it gives all the housedetails other wise between condition will be executed
Check out the outputs

Create Proc Getdeatils(@minroom int,maxroom int)
as begin
select * from tbl_HouseDetails
where (room=case when @minroom=-1 or @maxroom=-1 then room when room between @minroom and @maxroom then room end)
end

exec Getdeatils 1,2
gives the house details of 1,2,3,7,8,9
exec Getdeatils -1,1
gives the house details of 1,2,3,4,5,6,7,8,9,10
exec Getdeatils -1,-1
gives the house details of 1,2,3,4,5,6,7,8,9,10
exec Getdeatils 0,1
gives the house details of 1,4,5,8,9,10
exec Getdeatils 3,3
gives the house details of 6


Tag: case and between in where sql server, using case and between in where clause of sql server

Handling click event for TextBox in asp.net

<script language="javascript" type="text/javascript">
document.onclick = check;  
function check(e)
{
    var target = (e && e.target) || (event && event.srcElement);
    if(target.id!="txt")
    {
        if(document.getElementById("txt").value=="")
        {
            document.getElementById("txt").value="Enter Text Here";
            document.getElementById("txt").style.color='ActiveBorder';
        }
    }
    if(target.id=="txt")
    {
        document.getElementById("txt").style.color="Black";
        if(document.getElementById("txt").value=="Enter Text Here")
            document.getElementById("txt").value="";
    }
}
function Validate()
{
    if(document.getElementById("txt").value!="Enter Text Here" && document.getElementById("txt").value!="")
        return true;
    else
        return false;
}
</script>

<div>
   <asp:TextBox id="txt" runat="server" Text="Enter Text Here"  style="color:ActiveBorder;"></asp:TextBox>
   <asp:Button ID="btn" runat="server" Text="asdf" OnClientClick="javascript:return Validate();" />
</div>

Tag: Handling click event for TextBox in asp.net, text box wrapper event using javascript, textbox events using javascript in asp.net

How to invoke a server-side function from JavaScript in asp.net

Use  the code:

function check()
{
  document.getElementById("btnSAVE").click();
           or
  __doPostBack(document.getElementById('btnSAVE').name,'')
}

protected void btnSAVE_Click(object sender, EventArgs e)
{
  BusinessLogic.SaveDate();
}


Tag: calling server method from javascript, calling serverside method from javascript using asp.net, How to invoke a server-side function from JavaScript in asp.net

How to use Case statement in Where clause in SQL Server

Assume the table is like this

SNO	SNAME	GRADE	PASS
1	Kesav	1	1
2	Kishore	1	1
3	Srinath	2	1
4	Srinu	3	1
5	Sunil	1	0
6	Swapna	2	1
7	Venuka	0	1
8	Suneetha	0	1
9	Venky	0	1
10	Kamala	0	1

@grade input takes above_0/equallto_0/allpassed

Create proc normal_proc(@grade nvarchar(10))
as
begin
   if(@grade='above_0')
      select * from tab where grade in (1,2,3) and pass=1
   else if(@grade='equallto_0')
      select * from tab where grade in (0) and pass=1
   else
      select * from tab where pass=1
end

Now usage of case statement in where clause like follows....

create proc Case_in_Where(@grade nvarchar(10))
as
begin
   select * from tab
      where ((grade=case when @grade='above_0' then 1
         when @grade='equallto_0' then 0
         else grade end)
      or (grade=case when @grade='above_0' then 2
         when @grade='equallto_0' then 0
         else grade end)
      or (grade=case when @grade='above_0' then 3
         when @grade='equallto_0' then 0
         else grade end))
      and pass=1
end

You can follow either of the procedures above mentioned but comming to huge number of comparisions in where condition "Case_in_Where" is the best option


exec check_proc 'above_0'

SNO	SNAME	GRADE	PASS
1	Kesav	1	1
2	Kishore	1	1
3	Srinath	2	1
4	Srinu	3	1
6	Swapna	2	1

(5 row(s) affected)


exec check_proc 'equallto_0'

SNO	SNAME	GRADE	PASS
7	Venuka	0	1
8	Suneetha	0	1
9	Venky	0	1
10	Kamala	0	1

(4 row(s) affected)


exec check_proc 'allpassed'

SNO	SNAME	GRADE	PASS
1	Kesav	1	1
2	Kishore	1	1
3	Srinath	2	1
4	Srinu	3	1
6	Swapna	2	1
7	Venuka	0	1
8	Suneetha	0	1
9	Venky	0	1
10	Kamala	0	1

(9 row(s) affected)


Tag: case in sql server, case in sql server 2005, Case with Where clause in SQL Server, using case in sql server, using case in sql server where clause

Getting the Rows from DB in page wise using top clause in SQL Server

state_id  state_name
----------  -------------
1            California
2            Albama
3            Alaska
4            Arizona
5            Florida
6            Hawaii
7            Montana
8            Indiana
9            Lowa
10          New York
11          New Mexico
12          Texas
13          Virginia
14          South Carolina
15          Washington



Create the Procedure like

Create Proc GetRecords(@pageNo int=1,@pageSize int)
as begin
select top (@pageSize) state_id,state_name from tbl_State where state_id not in
(select top (@pageSize*(@pageNo-1)) state_id from tbl_State order by state_id)
order by state_id
end


Execution:-

exec GetRecords 1,4        --1 is page number & 4 is page size

state_id  state_name
----------  -------------
1            California
2            Albama
3            Alaska
4            Arizona

(4 row(s) affected)


exec GetRecords 2,4

state_id  state_name
----------  -------------
5            Florida
6            Hawaii
7            Montana
8            Indiana

(4 row(s) affected)


exec GetRecords 3,4

state_id  state_name
----------  -------------
9            Lowa
10          New York
11          New Mexico
12          Texas

(4 row(s) affected)


exec GetRecords 4,4

state_id  state_name
----------  -------------
13          Virginia
14          South Carolina
15          Washington

(3 row(s) affected)

for the same trick with row_number then check this url: http://mpurna.blogspot.in/2012/07/getting-rows-from-db-in-page-wise-using.html

Tag: Using Top clause in SQL Server