Posts

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 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 DEAL

How to convert rows into columns in Sql Server 2008

Image
Convert rows into columns in sql server database? We're going to see how to do that using different query, and there is the "Pivot" function in SQL Server 2005.     Example: We are using two 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 can use the "case"  statement in the query to convert rows into columns. 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) AS Physics, (CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry, (CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math, (CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English FROM Student_Subject) s GROUP BY StudentId Now, we can convert rows into columns