SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for the ‘General’ Category

SQL Server Reporting Tool – SqlAnswersQuery

Posted by Prashant on June 21, 2010


Here is a FREE tool SqlAnswersQuery you can use for SQL Server reporting purposes.  In fact this tool is also compatible with other databases like Microsoft Access, Oracle, MySQL and DB2

You can download it from http://www.sqlanswers.com/Software/SAQ/Default.aspx.

I found it interesting. Let me know your experience with this tool.

Posted in General, SQL Server, SQL Server Tools | Tagged: , , , , , , | 2 Comments »

My Windows is 32-bit or 64-bit Operating System?

Posted by Prashant on June 17, 2010


While installing a application to my machine, I come up to check my Windows bit count. So here is the quick post on how to find the Windows Bit Count.

The machine I was working was with Windows XP operating system and the steps are:

Go to Start >> Programs >> Accessories >> System Tools >> System Information.

(OR)

Start >> Run >> type “winmsd.exe” >> OK

Now under “System Summery” find an item named “System type”. If its value is x86-based then the CPU is 32-bit or if it’s value is x64-based then CPU is 64-bit (the image below is of a 32-bit machine).

For more information on this, visit http://support.microsoft.com/kb/827218.

Posted in General, Operating System | Tagged: , , | Leave a Comment »

Insert or Copy Data into Table

Posted by Prashant on June 11, 2010


This post is for a common situation most of us face while working with a database, which is copy/insert to a table.  We can use INSERT & SELECT to copy data into a database table in three ways.

 

  • INSERT Statement (Direct insert data values into the table):

Using INSERT statement we can add one data row at a time to destination table.

--Example: 1
CREATE TABLE Table01
(Column1 INT PRIMARY KEY, Column2 varchar(10))
GO

INSERT INTO Table01(Column1,Column2) VALUES(101,'A')
INSERT INTO Table01(Column1,Column2) VALUES(102,'B')
INSERT INTO Table01(Column1,Column2) VALUES(103,'C')
INSERT INTO Table01(Column1) VALUES(104)
GO

SELECT Column1,Column2 FROM Table01
GO
  • INSERT Statement with a SELECT Sub-Query:

If the destination table (we want to populate) is already created in the database, then using INSERT statement with a SELECT sub-query, we can copy data rows from one table to another.  The number of columns listed in INSERT statement must be same as the columns listed in SELECT statement and their data types must match.

--Example: 2
CREATE TABLE Table02
(Column1 INT IDENTITY, Column2 VARCHAR(10),
Column3 VARCHAR(8) DEFAULT('Welcome1'))
GO

INSERT INTO Table02(Column2)
SELECT Column2 FROM Table01
GO

SELECT Column1, Column2,Column3 FROM Table02
GO
  • SELECT with INTO Statement:

If the destination table (we want to populate) is not available in the database, then using SELECT INTO statement also it will create the table on the fly and copy data to it from the source table.

--Example: 3
SELECT Column1, Column2, Column3
INTO NewTable
FROM Table02
WHERE Column2 IS NOT NULL
GO

SELECT * FROM NewTable
GO

Please let me know your valuable comments.

Posted in General, Interview Questions, SQL Server | Tagged: , , , , , | Leave a Comment »

Condition based Update in SQL Server

Posted by Prashant on June 9, 2010


In SQL Server conditional update can be done using CASE.

--Update employee monthly commission based on their rating
UPDATE Employees
SET Comm =
CASE
WHEN (Rating between 2 and 3)THEN (Salary * 0.1)
WHEN (Rating between 3.1 and 4)THEN (Salary * 0.15)
WHEN (Rating between 4.1 and 5)THEN (Salary * 0.25)
ELSE 0.0
END

This example is just to demonstrate a scenario. Here it examines the employee rating to determine what should be the updated Commission. The CASE expression uses range of employee rating to update the commission amount.

Posted in General, Interview Questions | Tagged: , , , , | Leave a Comment »

Check existence of Table or Database before creating it in SQL Server

Posted by Prashant on June 9, 2010


To check if a table is exists or not a database:

--Check if table exists
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'TableName')
PRINT 'TableName available.'
ELSE
PRINT 'TableName not available.'

Similar way we can also check if a database is exists or not before creating.

--Check if database exists
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DBName')
PRINT 'DBName available.'
ELSE
PRINT 'DBName not available.'

Please leave your valuable comments on this.

Posted in General, SQL Server | Tagged: , , , , | 1 Comment »

Get List Of Files From a Windows Directory to SQL Server

Posted by Prashant on June 8, 2010


In SQL Server, we read data from single text file; excel file…etc. However we can extend this to read all the files in a particular directory. This post demonstrates a part of this scenario. Here we will discuss how to get the list of files from a particular directory, then can be extended to load them into SQL Server, which is not the scope of this post.

So in order to use xp_cmdshell, we need to enable it as it is disabled by default.

Here is the way to enable:

--allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
--Update the currently configured value for advanced options.
RECONFIGURE
GO
--Enable XP_CMDSHELL
EXEC sp_configure 'xp_cmdshell', 1
GO
--Update the currently configured value for this feature.
RECONFIGURE
GO

Refer Image-1 for the list of files in the directory. Now create a table and load the file list into it:

(Image-1)

--Create the table to store file list
CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256))
--Insert file list from directory to SQL Server
DECLARE @Path varchar(256) = 'dir C:\Import\'
DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B'
INSERT INTO myFileList
EXEC MASTER.dbo.xp_cmdshell @Command
--Check the list
SELECT * FROM myFileList
GO
--Clean up
DROP TABLE myFileList
GO

Here is the result in the table.

This can be extendable to other file operations too. Leave your valuable comments and suggestions.

Posted in General, SQL Server | Tagged: , , , , | 10 Comments »

Insert Data into table with two columns, one is Identity column and another is RowVersion

Posted by Prashant on June 7, 2010


This post is from a discussion in one of our SQL Server training session with Mr. Rakesh Mishra.  While discussing RowVersion data type, I have created a table with two columns one is of INT IDENTITY and another is of ROWVERSION type. Now the situations arises is how to insert data into that table?

Note:  ROWVERSION which is auto generated, unique binary number within a database and generally used for version stamping in table rows.

In the ROWVERSION column we can’t explicitly insert data, so can we insert data into the INDENTITY column without making the IDENTITY_INSERT to ON?

Yes, we can do that and here is the way it can be done.

--Create the table for demo
CREATE TABLE RowVersionTest(myID INT IDENTITY PRIMARY KEY, myRowVer ROWVERSION)

What if I will insert data into the IDENTITY column?

--Insert Data
INSERT INTO RowVersionTest(myID) VALUES (1)

MESSAGE:
Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'RowVersionTest' when 
IDENTITY_INSERT is set to OFF.

The above error message is because, we have not we can’t insert data into a IDENTITY column till IDENTITY_INSERT is OFF.  So here is the solution for this:

--Insert Data
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
--Select Data
SELECT * FROM #RowVersionTest

--Cleanup
DROP TABLE #RowVersionTest

Please leave your valuable comments.

Posted in General, SQL Server | Tagged: , , , | Leave a Comment »

 
%d bloggers like this: