SQL Server

Ashish Patel

Sql Query to check file exists

I recently came across the following usefull SQL query, Maybe you’ll find it useful.


declare @file_path	nvarchar(500)
declare @file_exists	int

set @file_path = 'C:\Temp.txt'

exec master.dbo.xp_fileexist 
	@file_path,
	@file_exists output 
Print	'File '+isnull(@file_path,'NULL')+' '+
	case when @file_exists = 1
	then 'exists'
	else 'does not exist'
	end

This actually check existence of file C:\Temp.txt 

Result:
File C:\Temp.txt exists

You can also use above query to check file Temp.txt file is exist. If YES Delete table or what ever…
Here is modified Query. 

declare @file_path	nvarchar(500)
declare @file_exists	int

set @file_path = 'C:\temp.txt'

exec master.dbo.xp_fileexist 
	@file_path,
	@file_exists output 
	
IF @file_exists = 1
BEGIN
    DELETE FROM [Ashish].[dbo].[Temp]
END

Thanks.

Ashish Patel

Trim not removing spaces in SQL

When I Was importing text file to SQL server, sometime it include spaces with data as well. And That space it not remove by using Trim function.These spaces appear as white spaces to our eyes but this is not the truth. When I check it with ASCII(char) it shows char 10- Line Feed(LF), 13 – Carriage Return(CR), 160 –nbsp. So the solution is to replace these characters with white spaces (CHAR(32)) and then apply the TRIM function to it. Below is the screipt for it:

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([ColumnName], CHAR(10), CHAR(32)),
                                        CHAR(13), CHAR(32)), CHAR(160),
                                CHAR(32)))) AS [ColumnName] 
FROM [TableName]

Update:: include tab – CHAR(9)

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([ColumnName], CHAR(10), CHAR(32))
                                  ,CHAR(13), CHAR(32))
                                  ,CHAR(160), CHAR(32))
                                  ,CHAR(9),CHAR(32))))
FROM [TableName]

Thanks
[ad#inpost]

Ashish Patel

Sql Server 2008 Introduction to Merge statement

Merge – One Statement for INSERT, UPDATE, DELETE

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH (  ) ] [ [ AS ] table_alias]
USING  ON
[ WHEN MATCHED [ AND  ]
THEN  ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND  ]
THEN  ]
[ WHEN NOT MATCHED BY SOURCE [ AND  ]
THEN  ]
[  ]
[ OPTION (  [ ,...n ] ) ]
;

Example:
Let’s create Student Details and StudentTotalMarks and inserted some records.

Student Details:

USE AdventureWorks
GO
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO

In our example we will consider three main conditions while we merge this two tables.

  1. Delete the records whose marks are more than 250.
  2. Update marks and add 25 to each as internals if records exist.
  3. Insert the records if record does not exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

There are two very important points to remember while using MERGE statement.

  1. Semicolon is mandatory after the merge statement.
  2. When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.

AS we can see there are 5 rows updated. StudentID 2 is deleted as it is more than 250, 25 marks have been added to all records that exists i.e StudentID 1,3 and the records that did not exists i.e. 4 and 5 are now inserted in StudentTotalMarks .

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.

Reference : Pinal Dave (http://blog.SQLAuthority.com)