ProgrammingOcean

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.

In sql server there are 2 built-in stored procedures for drop the already existing backup device and create the new device in the user defined path. Before create the backup device, must drop the device. Because when you create a backup device, if backup device had already been created, sql server throw a error. So very first time have to create a backup device manually. Afterwards you can use this script. This is very use ful as user can take backup where user wants it since this procedure takes the path as parameter. Create Backup device manually in Sql Server 2008 Go to Server Object where right click on Backup Device, Then choose New Backup Device. if you choose that, sql server let you to create the New Backup Device. (Please refer the figures as shown below) Figure 1 Figure 2 Figure 3 Figure 4 Figure 5 Script for Drop the Backup Device EXEC sp_dropdevice 'Time_Attendance' Script for Create the Backup Device EXEC sp_addumpdevice 'disk', 'Time_Attendance', @...

This very frequent needful thing for developers as they need to create so many reports based on this concept. For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This would add each and every vale with previous value and give like Running Total. Query for this, SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY, SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL FROM PRODUCT_LOCATION WHERE CLIENT_C=UPPER(‘MSWG’) AND UNRESTRICTED_QTY>0 AND LOCATION_NO=’RECEIPT_BAY‘ row in the result set and adding up the values with currently reading value which is specified by CURRENT ROW up to last record of the record set. And ordering results by PRODUCT_NO The result of the above query shown below.

Here it s the query for retrieve the table structure in sql server, SELECT Ordinal_Position,Column_Name,Data_Type,Is_Nullable,Character_Maximum_Length INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table Name'

drop table ##temp create table ##temp (id char(3) ,marks int ) create table ##temp2 (id char(3) ,marks int ) insert into ##temp(id,marks) ----Here we are inserting duplicate select '001',50 ----records for each ID union all select '001',60 union all select '002',66 union all select '002',88 union all select '003',92 union all select '003',64 union all select '004',44 union all select '005',67 ----Here we are getting the distinct records and insert then into another Tempory table insert into ##temp2 select distinct id,max(marks) from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) group by id ---And delete those duplicate records from original Table delete from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) ---And again inser the inserted reocrds from temporary Table insert into ##t...

Blogger Buzz: Blogger integrates with Amazon Associates

We can use this clause to sum/count/avg/max/min so on. This clause will give you the output as detail and summary which is based on the fields you want to summarize. select * from #temp order by student compute sum(marks) by student in above compute by clause, you must specify the field you want to sum in Compute clause and specify the field in By clause based on which field you need to compute. Very important thing is you must specify the Order By clause in which specify the fileds whatever you specify in By clause in Compute clause. The output of above query is, When we try with max,min,avg, the query and output would be as shown below, Using Max() select * from #temp order by student compute max ( marks ) by student Using Min () select * from #temp order by student compute min ( marks ) by student Using Avg() select * from #temp order by student compute avg ( marks ) by student