Showing posts with the label Sql Server

Database backup from Sql script using cursor in sql server 2008/2010

This is simple example of taking database backup from the T-sql script. In my example i have illustrated the simplest way to take the back up from the database. I have used the cursor for the same. Following is the code of the Backup script. DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALL

How to convert rows into columns in Sql Server 2008

How to convert rows into columns? We're going to see how to do that using query, and there is the "Pivot" function in SQL Server 2005. The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns. If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns: Example: We have 2 tables: Subject which is a lookup table that holds the subject names, and the StudentSubject which contains the student grades for the different subject. We are going to build the query having fixed columns: We use the "if-else" functionality in the query to put them in the defined columns (in SQL, it is "case"): SELECT StudentId, SUM(Physics) AS Physics, SUM(Chemistry) As Chemistry, SUM(Math) AS Math, SUM(English) As English FROM (SELECT StudentId, (CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) A