Thursday, April 18, 2013

Modifying Data in Tables through a View in SQL Server

Can we modify the table data through SQL View? 

The answer is: Yes !

We can modify the data of an underlying base table through a view, in the same manner as we modify data in a table by using UPDATE, INSERT and DELETE statements or by using the BCP utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:

·         Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

·         The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:

1.       An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).

2.       A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.

·         The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

·         TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.

Friday, April 5, 2013

Concatenate strings in MS-Excel

Here we will learn how we can easily concatenate string in MS-Excel which could be useful in many situations. I am going to describe a situation which I encountered once.

Let us assume a situation, we have a huge list of numbers (say Roll number of students in a university) saved in MS-Excel sheet and we want to write a SQL query that updates the section of all the students in that huge list. This we could do easily with a update SQL query using “IN” operator but problem is that we need to have our Student’s Roll number list in a format like ‘RollNum1’, ‘RollNum2’, ‘RollNum3’….. and in Excel sheet, we have a just a list of Roll numbers. Just for sake of understanding the scenario, a sample update query is given below:
UPDATE               TSTUDENTS
SET                         STUDNT_SECTN  =  ‘C2’

WHERE                  ROLL_NUM IN (a comma separated list in which each element is apostrophe enclosed)
So what’s next? Let’s go for the concatenation of strings in MS-Excel. We could do concatenation in Excel by following ways:
(1)    Suppose we have a list in column-A

(2)    On cell B-1, type “,”

(3)    Then Fill the entire column –B using AutoFill handle or select the column-B and use Ctrl+D option to auto fill rest of the cells in column-B

(4)    Now we could apply our concatenation in column-D. Got to Cell D1.

(5)    Concatenate the string either using string built-in concatenate function                

=CONCATENATE("'", A1, "'",B1)  
Or we could use “&” symbol for concatenation as   ="'" & A1 & "'" & B1
(6)    Then Fill the entire column –D using AutoFill handle or select the column-B and use Ctrl+D option to auto fill rest of the cells in column-D.

(7)    That’s it, we are done!
  A                     B             C                 D

B12 9



'B12 9',

B13 0



'B13 0',

B13 8



'B13 8',

B13 9



'B13 9',

B14  6



'B14  6',

B14 4



'B14 4',

B14 5



'B14 5',

B45 0



'B45 0',

B45 9



'B45 9',

B46 1



'B46 1',

B46 2



'B46 2',

B46 3



'B46 3',

B47 5



'B47 5',

B47 6



'B47 6',

B48 7



'B48 7',

B49 5



'B49 5',

B49 6



'B49 6',

B5 4



'B5 4',

B5 5



'B5 5',

B5 6



'B5 6',

B5 7



'B5 7',

B50 4



'B50 4',

B6 4



'B6 4',

B6 5



'B6 5',

B6 6



'B6 6',