A myth about view TIP #102


I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ?

I  hope everyone who is reading this article will be aware of what is view and how to create it ?

If not then need not to worry

“View can be see as a virtual table based on SQL SERVER result or in other world it is a layer above actual data tables” Or we can say when you want to hide actual table then you create a view wrapper”

You can easily create a View with following syntax




Let’s understand this by an example.

Suppose in I have a database with name “IndiandotnetDB” which has a  table “tblStudentSource”

Now I created a  view just for fetching records from tblStudentSource


CREATE VIEW StudentSourceView
SELECT StudentId,
FROM tblStudentSource


Now you can fetch records directly from  View as shown in below

SELECT * FROM StudentSourceView

You will get all the records from tblStudentSourceView

Now the Question “ Can you Insert record from View ?“

So the answer is specific condition you can.

In our scenario we will write following command and execute so the record will be added


So you are clear that we can Insert records from View.

In similar fashion we can update the records as shown below figure

We will following SQL statement as showing in figure in which we are going to update record no 2004 as shown in above figure


As shown record 2004’s FirstValue is updated to value “Updated”


Now in similar way we can delete the record using View.

Although there are certain other factor due to which “Insert/update/delete” is possible.

like we have only simple schema.

I will describe this later with more detail like  scenario where  we can not Insert/Uppdate/delete using view.

Till than Enjoy !!!





Bulk Copy Program (BCP)–A simple way to export data in a file TIP#101

Let’s consider a scenario where the end user require a CSV file of all the records in a table then BCP is one of the simplest way.

BCP stands for Bulk copy program. By the name you got the feeling of lots of data Smile.

Although, there are various options & parameters available with BCP but here we will talk about simple one Smile.

To understand it more clearly lets consider an example.

I have a database with name “IndiandotnetDB” and in this database I have a table with name tblStudentSource which having few records as shown in below figure


Now, the objective here is to export all the data which is in tblStudentSource table to a CSV file.

to achieve this we will use BCP command.  We are running BCP from command prompt as shown in below figure


Although there are various other options available with BCP command but for current post I choose simple one .

Now let me explain the command

BCP IndiandotnetDb.dbo.tblStudentSource OUT d:\File\output\sampleExport.csv –S . –T –c

so in above command “IndiandotnetDB” is my database

“dbo” is schema

“tblStudentSource” is  a table whose data need to be export

OUT – here out stands for export (export the table or query data)

“D:\File\output\” is folder where I want exported file

“sampleExport.csv” is file name which will be created by BCP and contains entire records after execution of BCP command

-S is stands for server I used “.” for localhost we can provide instance name as well

-T is for trusted connection (we can use – U for username – P for password)

-c is stands for character data type if you are not using this then you have to provide data type for each fields of output query result

Below is the output of the command which we run “ a sampleExport.csv file with all the records”


I hope this simple post will help you some where. I will share more details about BCP command in coming post.



Find all the dates in a date range ? TIP #100


It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = ‘2005-07-01’
DECLARE @EndDate   AS DATE = ‘2005-07-29’
DECLARE @tblDateRange AS TABLE (salesDate DATE)
SET @SeedDate = ‘2005-07-01’
WHILE @SeedDate <= @EndDate
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
SELECT * FROM @tblDateRange


Now second and interesting approach

DECLARE @StartDate AS DATE = ‘2005-07-01’
DECLARE @EndDate   AS DATE = ‘2005-07-29’
DECLARE @tblDateRange AS TABLE (salesDate DATE)

AS (
SELECT @StartDate AS SalesDate
SELECT DATEADD(d,1, SalesDate) As salesDate
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)

SELECT * FROM @tblDateRange


These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.



Enjoy !!!