Monthly Archives: July 2014

Drop table if exists

Posted on by

This is just a simple way to save some lines on code, specially when we need to drop and recreate a table.

Dropping a table can be easily done with:

DROP TABLE dbo.MyTable

But if the table doesn’t exists, it will throw an error stopping the execution of your query or stored procedure.

That’s why we need to check if the table exists first:

IF EXISTS (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'My table')
	BEGIN
		DROP TABLE dbo.MyTable
	END

And that becomes a little annoying if you need to drop many tables, so here is a simple stored procedure that will do that for you:

CREATE PROCEDURE dbo.usp_DropifExists
    @table varchar(150)
AS
BEGIN
	DECLARE @SQL varchar(max)
	IF EXISTS (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @table)
	BEGIN
		SET @SQL = 'DROP TABLE '+@table
		EXEC(@SQL)
	END
END

USE:

EXEC dbo.usp_DropifExists ('MyTable')

Replace a value in all tables

Posted on by

Someone asked for a way to replace a value in all the tables, the issue was the following:

I have a database that has multiple tables with an old URL and I need to update it to the new one, old URL (www.site.com) new URL (www.newsite.com), there are many tables and they have also multiple columns, is there a way to do this programmatically?

The problem above requires basically nested loops, first to get all the tables, then to get all the columns for each table, and finally do the replace.

I wrote the following solution:

Peter Griffin

Continue reading “Replace a value in all tables” »

Share temp tables to other sessions

Posted on by

I know what you might be asking…

Why would I want to access someone else’s temp tables?

how absurd

 

Well yeah, it would be really unnecessary (and creepy) but it is most commonly used on dynamic SQL specially because temp tables created inside a dynamic SQL statement don’t share the same session of the stored procedure that is calling the dynamic SQL, here is a basic example:

Continue reading “Share temp tables to other sessions” »

How to get Date and Time Difference

Posted on by

Do you want to know how many days are between 2 given dates?

Or how many months, weeks, hours, minutes or seconds?

Easy peasy!

Just check the following examples:

SELECT DATEDIFF(MONTH, GETDATE(), GETDATE() + 1) AS MonthDiff
SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff 
SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff
SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff

 

Syntax: DATEDIFF ( datepart , startdate , enddate )

SQL DateDiff

SQL DateDiff


Export Table to CSV Manually

Posted on by

Export a SQL table to CSV file (Comma Separated Value) or any other file is something very common and easy to do, but still I’ve seen people having some common mistakes.

Here is the basic way to export a table to a CSV file.

This process is very similar if you want to export it to any other file extension like Excel, Access, txt, or more…

Step 1: Right click the database name and navigate to Task -> Export Data

Export step 1

Export step 1

Continue reading “Export Table to CSV Manually” »

EXEC() vs sp_executesql()

Posted on by

These functions are commonly used for the same thing which is execute a piece of SQL code or stored procedure, most commonly used for dynamic SQL.

So lets see a usage example for both:

EXEC ( )

DECLARE @SQL varchar(max)
DECLARE @Tablename varchar(20)

SET @Tablename = 'Person.Address'
SET @SQL = 'SELECT * FROM '+ @Tablename

EXEC (@SQL)

sp_executesql ()

DECLARE @SQL nvarchar(max)
DECLARE @Tablename nvarchar(20)

SET @Tablename = 'Person.Address'
SET @SQL = 'SELECT * FROM '+ @Tablename

exec sp_executesql @SQL

In the above examples there is just one slight difference, @SQL variable is varchar for EXEC and nvarchar for sp_executesql.

But what are the main differences between them?

Continue reading “EXEC() vs sp_executesql()” »

Temp tables vs Table variables

Posted on by

Temp tables and Table variables are common and you probably have seen the usage of both in stored procedures, so if you are here is because you want to know what exactly is the difference between them.

Both have a very similar functionality, which is to store data temporarily, but lets see a usage example of both first.

 

#temp table

CREATE TABLE #HOUSE_LANNISTER (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	FIRSTNAME VARCHAR(50),
	LASTNAME VARCHAR(50)
)

INSERT INTO #HOUSE_LANNISTER VALUES('Tywin','Lannister')
INSERT INTO #HOUSE_LANNISTER VALUES('Tyrion','Lannister')

@temp table variable

DECLARE @HOUSE_LANNISTER TABLE (
ID INT IDENTITY(1,1) PRIMARY KEY,
	FIRSTNAME VARCHAR(50),
	LASTNAME VARCHAR(50)
)

INSERT INTO @HOUSE_LANNISTER VALUES('Tywin','Lannister')
INSERT INTO @HOUSE_LANNISTER VALUES('Tyrion','Lannister')

Both examples create temporary tables and you can insert data into those, but here are the main differences: Continue reading “Temp tables vs Table variables” »

Encapsulate SQL code

Posted on by

On many .Net programming languages there is something called “Code Outlining” better known as #regions  which is a way to group or encapsulate pieces of code,  very helpful to have a clean code whenever you have hundreds or thousands of code lines, here is how it looks on C#.

c# region

c# region

Unfortunately regions are not available on MS SQL, because it is not necessary :)

Continue reading “Encapsulate SQL code” »

US States and Cities MSSQL dump

Posted on by

Useful all the times specially if you work or study in the U.S. here are some SQL query files that you can execute to create a States and Cities tables (US only).

Download US States SQL table

Download US Cities SQL table

 

If you wish you can contribute with other countries lists by posting a URL in the comments below.

 

Thank you!

UPDATE with a JOIN

Posted on by

You probably already know how to do a simple SQL UPDATE when you just need to update the data in a table.

UPDATE MyTABLE SET COL1 = 0

But what if the data you want to update depends on the data from another table.

For this example, using AdventureWorks sample database, lets say you want to UPDATE all Employees from “Engineering” , you know what data to update by selecting the records with a JOIN:

SELECT e.* FROM [HumanResources].[Employee] e
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] dh
	ON e.BusinessEntityID = dh.BusinessEntityID
INNER JOIN [HumanResources].[Department] d
	ON dh.DepartmentID = d.DepartmentID
WHERE d.Name = 'Engineering'

To update that set of records, just change the  SELECT part for an UPDATE with the alias of the table to update:

UPDATE e
SET e.VacationHours = 20
FROM [HumanResources].[Employee] e
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] dh
	ON e.BusinessEntityID = dh.BusinessEntityID
INNER JOIN [HumanResources].[Department] d
	ON dh.DepartmentID = d.DepartmentID
WHERE d.Name = 'Engineering'