Delete statement with JOIN in SQL Server 2005

We often need to delete data from table based on JOIN.  In that case, what we do is, try to execute SELECT query with JOIN, we find and confirm the records we want to delete and then remove SELECT statement and write DELETE instead. But this will not work in case of JOIN. We need to give table alias explicitly to tell SQL Server Engine that what table we need to include in delete. Have a look at following case.

–create table1 for demo
create table Orders1
(
      OrderID INT not null constraint pk_ordid primary key,
      orderdate datetime
)
–create table2 for demo
create table orderDetails1
(
OrderDetailsID int not null,
OrderID INT not null constraint fk_ordid references Orders1(OrderID)
)
–insert records
insert into orders1
select 1,getdate() union all
select 2,getdate() union all
select 3,getdate() union all
select 4,getdate()
insert into orderdetails1
select 1,3 union all
select 2,2 union all
select 3,3 union all
select 4,1 union all
select 5,2
GO
–try to select records which you want to delete
select o.OrderID,o.orderDate,od.OrderDetailsID
from Orders1 o inner join orderdetails1 od
on o.orderid=od.orderid
order by o.orderid
–now try to delete records by removeing
–SELECT statement and put DELETE instead.
–it will not work, as SQL Engine will be confused,
–what to delete, orders1? or orderdetails1?
delete
–select o.OrderID,o.orderDate,od.OrderDetailsID
from Orders1 o inner join orderdetails1 od
on o.orderid=od.orderid
–order by o.orderid


–here is the solution, you have to specify
–I need to delete from od (OrderDetails)
delete od
from Orders1 o inner join orderdetails1 od
on o.orderid=od.orderid
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Load Relational XML data in SQL Server 2005

When question comes to play with XML, even seasoned programmer try to get away from it, not because it is difficult task but lack of knowledge. I promise, loading XML data into SQL Server 2005 as a relational data, is not going to be as difficult as you are thinking.
Let me show you one example which will fill two relational tables in SQL Server from XML data. Let us first create one XML file at C:\vendor.XML with following data in it.
   <vendor>
<c>
  <venID>101venID>
  <first_name>Jamesfirst_name>
  <last_name>Butlerlast_name>
<po>
  <poid>1001poid>
  <venID>101venID>
  <po_date>2008-04-01T00:00:00po_date>
  <price>400.00price>
  po>
<po>
  <poid>1002poid>
  <venID>101venID>
  <po_date>2008-05-01T00:00:00po_date>
  <price>299.0000price>
 po>
<po>
  <poid>1002poid>
  <venID>101venID>
  <po_date>2009-04-01T00:00:00po_date>
  <price>600.0000price>
po>
 c>

vendor>

<

Now, create two tables to feed vendor details and purchase order detail, than we will load XML file into both tables.
–create table to hold Vendor information
CREATE TABLE vendor (
        venid INT NOT NULL
        , first_name VARCHAR(50)
        , last_name VARCHAR(50)
        )
–create table for holding Purchase order information.
CREATE TABLE PurcharOrder (
        poID INT NOT NULL
        , venid INT NOT NULL
        , po_date DATETIME
        , price MONEY
        )
GO
–hold xml from file
DECLARE @x XML
SET @x= (
SELECT xm.Col1 FROM OPENROWSET(BULK ‘c:\vendor.xml’,SINGLE_BLOB) AS xm(Col1)
         )
— show file contents
select @x
INSERT INTO vendor
SELECT
ven.value(‘./venID[1]’, ‘INT’) as vid
, ven.value(‘./first_name[1]’, ‘VARCHAR(50)’) as first_name
, ven.value(‘./last_name[1]’, ‘VARCHAR(50)’) as last_name
–, C.query(‘.’) 
FROM @x.nodes(‘/vendor/c’) tab(ven)
INSERT INTO PurcharOrder
SELECT
ven.value(‘./poid[1]’, ‘INT’) as POID
, ven.value(‘./venID[1]’, ‘INT’) as VenID
, ven.value(‘./po_date[1]’, ‘DATETIME’) as po_date
, ven.value(‘./price[1]’, ‘MONEY’) as price
–,ven.query(‘.’)
FROM @x.nodes(‘/vendor/c/po’) tab(ven)
GO
–check both table, whether data comes or not.
select * FROM Vendor
Select * from PurcharOrder


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Fix Error: Msg 8116, Level 16, State 1, Line 1 Argument data type ntext is invalid for argument 1 of replace function.

Have you ever come across MSG 8116? It is really annoying error in SQL Server 2000 and SQL Server 2005, when you try to perform some string operation on fields having NTEXT as column data type, you most probably greeted with error something like header of the article.  Let us see how the error comes and how to eliminate it?
USE [adventureworks]
GO
if exists (select * from dbo.sysobjects where id =object_id(N‘[dbo].[EMPS]’) and OBJECTPROPERTY(id, N‘IsUserTable’) =1)
DROP TABLE Emps
GO
CREATE TABLE [dbo].[emps](
[Name] [ntext],
[Dept] [varchar](10),
[Company] [varchar](15)
) ON [PRIMARY]
GO
–insert records
INSERT INTO emps
SELECT ‘ RITESH shah ‘,‘MIS’,‘ECHEM’ UNION ALL
SELECT ‘ Rajan ‘,‘MIS’,‘mar’
GO
select rtrim(name) from emps
–or
select replace(name,‘ ‘,) from emps

–you will be greeted with following error in above queries
–Msg 8116, Level 16, State 1, Line 1
–Argument data type ntext is invalid for argument 1 of rtrim function.
–there solution is:
–either you convert your column physically to varchar or cast it to varchar while querying
select replace(convert(varchar(25),name),‘ ‘,) from emps
–or
select ltrim(rtrim(convert(varchar(25),name))) from emps

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Stored Procedure return value handle and record set handle in C# ASP.NET

If you are SQL Server geek, you must be aware with Stored Procedure. While question comes to implement stored procedure in front end language, many developers makes mistake or feel confusion as per my recent observation so I am tempted to write one article which can show how to handle stored procedure in C# when it return some value and when it return some record sets.

–CREATE TABLE

CREATE TABLE empDemo

(

ID INT NOT NULL,

NAME VARCHAR(50)

)

GO

–INSERT DATA

INSERT INTO empDemo

SELECT 1,‘Ritesh’ UNION ALL

SELECT 2,‘Rajan’

–create SP which will return list of employee

–NOTE: this is just basic SP, you can create

–your own for your custom need.

CREATE PROC ListEmp (@id INT)

AS

BEGIN

SELECT * FROM empDemo WHERE ID=@id

END

–check SP whether it works.

EXEC ListEmp 2

Now, we will see C# code (in asp.net code behind) to return the list of employee:

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “ListEmp”;

command.Parameters.AddWithValue(“@id”, “2”);

//create one data adapter which will execute the command and fill the data into data set

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

recordAdp.SelectCommand = command;

System.Data.DataSet recordSet = new System.Data.DataSet();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

//bind gridview1 with our dataset

GridView1.DataSource = recordSet.Tables[0].DefaultView;

GridView1.DataBind();

}

This seems basic but really useful for newbie, now what if we have some other DML command and we want to return value from SP and handle it in our C# ASP.NET.

–create SP to insert record and retun value

–we will handle return value in C# code behind

CREATE PROC InsertEmp(@ID INT, @Name VARCHAR(20))

AS

BEGIN

BEGIN TRY

INSERT INTO empDemo(ID,Name) VALUES(@ID,@Name)

return 1

END TRY

BEGIN CATCH

return 0

END CATCH

END

–check SP works or not

EXEC InsertEmp 3,‘Alka’

Now let us move to ASP.NET C# code behind.

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “InsertEmp”;

command.Parameters.Add(“@ReturnValue”,System.Data.SqlDbType.Int);

command.Parameters[“@ReturnValue”].Direction = System.Data.ParameterDirection.ReturnValue;

command.Parameters.AddWithValue(“@id”, “4”);

command.Parameters.AddWithValue(“@Name”, “Bihag”);

command.ExecuteScalar();

int i = Convert.ToInt32(command.Parameters[“@ReturnValue”].Value);

if (i == 1)

{

Response.Write(“Successfull!!!!”);

}

else

{

Response.Write(“Not Successfull!!!!”);

}

}

Hope you have enjoyed it!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between Stored Procedure and User Define Function in SQL Server 2005:

I have written many articles on differences and comparison on various topic of Microsoft SQL Server. Here is one more difference between Stored Procedure and User Define Function. Many times I have seen new developer in confusion about usage of functions so after finishing the series of articles in stored procedures and user define functions I thought to write one article for these differences.

Actually before Microsoft SQL Server 2000, there was no concept of UDFs and it has first been revealed in Microsoft SQL Server 2000 to wrap up your complex T-SQL logic but initially people were not started adopt it but I guess they have started focusing in it with Microsoft SQL Server 2005 when UDFs comes up with new features like table valued function and CLR function implementation. Now, the situation is, UDFs become one powerful tool in SQL Server kit.

The main difference I feel between SPs and UDFs is usage in SELECT. One can use UDFs anywhere in JOIN, FROM, WHERE and HAVING clause whereas SPs are not that much flexible.

UDFs are simple to invoke in any T-SQL statement then SPs.

Some DML statements like INSERT, UPDATE and DELETE are not permissible in UDFs whereas you can use it in SPs.

You can not call non-deterministic function of SQL Server inside the UDFs whereas you can call them in SPs. For example GETDATE() function, it is non-deterministic and can’t be called within UDFs.

Stored Procedure can call function but function can’t call stored procedure.

User defined function can have only input parameter whereas SPs can have input as well as output parameter.

You can use Try…Catch in SPs whereas UDF can’t support it.

If you are more interested to learn about SPs and UDFs than you can sear http://www.sqlhub.com for more details as I wrote dozens of article on this topic with so many real life examples.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Scrollable Dynamic Cursor with FETCH_FIRST, FETCH_LAST, FETCH_PRIOR, FETCH_NEXT in SQL Server 2005

This is continuous topic of CURSOR. You can refer my past article on cursor at

http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html

http://ritesh-a-shah.blogspot.com/2009/03/forwardonly-and-fastforward-cursor.html

http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

I gave definition of CURSOR and explained different type of CURSOR in my first article given above. Second article was for FORWARD_ONLY cursor and third one was for dynamic cross tab query with FAST_FORWARD cursor. Now this article will show you example of dynamic cursor which can move forward and backward as well. It will get fresh record set with every FETCH statement so that while fetching the record, we will be able to get fresh modified record by another user for our SELECT statement in SQL Server 2005.

Let us see it practically.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

DECLARE @Counter INT

SET @Counter =1

DECLARE @strName VARCHAR(200)

DECLARE @empCursor CURSOR

–declaring SCROLL cursor

–which will move first, last, forward and backward

SET @empCursor = CURSOR SCROLL FOR

SELECT Name FROM emps ORDER BY NAME

OPEN @empCursor

FETCH NEXT FROM @empCursor INTO @strName

–user of FETCH NEXT

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @strName + NEXT’

FETCH NEXT FROM @empCursor INTO @strName

SET @Counter = @Counter + 1

END

–user of FETCH PRIOR

WHILE (@Counter > 1)

BEGIN

FETCH PRIOR FROM @empCursor INTO @strName

PRINT @strName + PRIOR’

SET @Counter = @Counter 1

END

–user of FETCH FIRST

BEGIN

FETCH FIRST FROM @empCursor INTO @strName

PRINT @strName + FIRST’

END

–user of FETCH LAST

BEGIN

FETCH LAST FROM @empCursor INTO @strName

PRINT @strName + LAST’

END

CLOSE @empCursor

DEALLOCATE @empCursor

GO

Note: this is very resource consuming cursor so think twice before use it.

Reference: Ritesh Shah/Rashmika Vaghela

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

FORWARD_ONLY and FAST_FORWARD Cursor example in SQL Server 2005:

My previous article was on cursors, it has covered different types of cursor with its definition. You can refer it at:

http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html

As I promised in my above article that I will provide with some examples of cursor and here I am with example

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–script for FORWARD_ONLY Cursor

DECLARE @strName VARCHaR(15)

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR SELECT Name FROM emps

OPEN FirstCur

FETCH FROM FirstCur INTO @strName

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @strName

FETCH NEXT FROM FirstCur INTO @strName

END

CLOSE FirstCur

DEALLOCATE FirstCur

go

I have created one script for dynamic cross tab query in one of my previous article. I have used FAST_FORWARD cursor in that script. It will be nice example of cursor. You can refer it at

http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

What is CURSOR? Definition of CURSOR in SQL Server 2005:

CURSOR is a server side tool and completely different from ADO.NET’s cursor. It is giving row-by-row solution to the result set and let me tell you that SQL Server impressive with handling set of rows, not row-by-row. This is useful for those who came from procedural background and don’t much familiar with set-based relational algebra.

You can define CURSOR as read only or update but read only is fast as compare with update as read only will gives your data and won’t remember it.

Personally I used to avoid cursor as long as it is possible as it uses lots of resources of server and reduce the performance. Whenever it is possible use temp table, derived table, sub-query, CASE statement but finally you draw conclusion that any of the set-based operation won’t work for your problem than and than go for CURSOR solution but make sure you are not selecting more than necessary rows in CURSOR. Lesser the row, higher the performance!!!!

The main reason for writing this article is to help those who want to understand the concept of CURSOR and another reason CURSOR is not something that you can completely ignore. There may be some situation where you have to use CURSOR, may be some complex logic or dynamic code iteration especially while making code generator.

Type of CURSOR:

Static: This is lowest type of CURSOR and used to use for finding data and generating reports. Once getting the data into the CURSOR you will not identify any modification done in data after retrieving it because it make a copy of your data into the temp table of tempDB.

Forward_Only: This is the default type of CURSOR and really very similar to Static CURSOR the only difference you will find it, it will move forward only. In short, this CURSOR will scroll from first to last no other movement supported.

Fast_Forward: this is a mixture of Forward_Only and Read_Only.

Dynamic: this CURSOR will be bit slow as it will accept any data modification done by any user even after you fetch the data because you scroll around the CURSOR. Data membership, value and its order will be changed in each FETCH if any data modification has been done in record set.

Keyset-Driven: when you open this CURSOR, membership key and order of row are fixed in CURSOR.

Steps for CURSOR:

DECLARE: Defines a CURSOR with standard SELECT statement. This must be done before you open the CURSOR.

OPEN: physically open the CURSOR and received the record set exist in table at the time of opening the CURSOR.

FETCH: CURSOR always points to one row at a time and FETCH is retrieve the value from that row to manipulate it further.

CLOSE: CLOSE will release the lock on table made by CURSOR. If you wish than you can re-open CURSOR after closes it.

DEALLOCATE: Once you are done with CURSOR, do DEALLOCATE it and removes the memory from the server. You can open the CURSOR once you close it but can’t re-open CURSOR once you DEALLOCATE it.

You can run following query for dynamic management function in-order to get information about CURSOR running on your server.

select * from sys.dm_exec_cursors(0)
go

I will give examples to make your concept more clearly about different type of CURSOR in my future article.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Bulk Insert – Bulk Copy or BCP (Difference and Comparison) in SQL Server 2005

Now I wrote quite a few articles on BULK COPY and BULK INSERT commands so I thought to give one separate article for differences between BULK INSERT and BULK COPY (BCP).

Let me start with its type. BULK INSERT is a SQL command and BCP is a separate utility outside SSMS and you need to run BCP from DOS prompt (command prompt).

BULK INSERT can copy data from flat file to SQL Server’s table whereas BCP is for import and export both. You can copy data from flat file to SQL Server and from SQL Server to Flat file with the help of BCP.

You can use INOUTQUERYOUT argument with BCP to import, export and conditional import and export which in not possible in BULK INSERT.

In BULK INSERT there is no support for down level version input whereas it is possible in BCP.

BCP has less parsing efforts and cost than BULK INSERT.

Apart from above differences, both are almost same and give almost same performance moreover, both are single threaded, no parallel operation allowed.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Bulk Operation or BULK Insert in SQL Server 2005:

When you have big flat file may be CSV with lots of records may be couple hundred mega byte file and you wants to transfer those data into SQL-Server than you are at the right article as bulk insert is one of the way to get this kind of heavy lifting.

It is really very efficient and fast as it by passes the transaction and directly injects the data into data file. Let us try by creating one .CSV file (a.k.a. comma delimited file or comma separated value) and load it in to SQL Server database table.

Open your notepad.

— Copy following data in it. Below given are a dummy data and represent name, department and company name.

Ritesh,MIS,echem
Bihag,MIS,CT
Rajan,account,Marwadi
Alka,account,tata
Pinal,DBA,sqlautho
Alpesh,Chemical,echem

— Save the above notepad file as “emp.txt” in your “C” drive.

— Open your SSMS and create following table and use BULK INSERT command as follows:

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–BULK INSERT to insert file’s data to emps table

BULK INSERT emps

FROM ‘c:\emp.txt’

WITH (FIELDTERMINATOR=‘,’,FIRSTROW =0, ROWTERMINATOR=‘\n’);

–FIELD TERMINATOR defines the separator it could be any like ‘,’ or ” etc.

–FIRSTROW defines from where it should read data, first line of the file may be

–header so SQL shouldn’t isert it.

–ROWTERMINATOR is new line characte ‘\n’, it could be different if you have got the

–file from mainframe PC or other system it could be different, you can see it in hex editor

–test the data whether it has really been copied.

SELECT * FROM emps

So this is all about BULK INSERT. It is really very fast. Once I did BULK INSERT for about 100,000 records and it hardly took 2 minutes. Enjoy bulk operation!!!!

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah