Tuesday, 29 July 2014

Create temp table in SQL Server

     SQL Server provides the feature temporary table which helps user to replace Stored Procedure that requires large number of row manipulation and to replace complex SQL Join operations. The temp table can be created at runtime and can do all kind of operations like normal table. But, as its name implies, the scope is limited.

SQL Server provides two types of temp tables based on its connection life time.

Local Temp Table

Local temp tables are only available to the current connection for the user, and are deleted when the connection is closed. it requires the single hash value "#" as the prefix when created.

Syntax: (CREATE TABLE #MyTempTable).
--Create Local Temp Table
CREATE TABLE #UserTempTable(
UserID int, UserName varchar(250), 
Email varchar(250))

--Insert rows into Local Temp Table
Insert into #UserTempTable values(1,'Morgan','Moragn@MorganTechSpace.com')
Insert into #UserTempTable values(2,'Tim','Tim@MorganTechSpace.com')
Insert into #UserTempTable values(2,'Shiv','Shiv@MorganTechSpace.com')

--Select rows from Local Temp Table
Select * from #UserTempTable
How to Create temp table in SQL Server

Global Temp Table

Global Temp tables are visible to all connections of SQLServer, and only destroyed when the last connection referencing the table is closed. it requires the double hash value "##" as the prefix when created.

Syntax: (CREATE TABLE ##MyTempTable).
--Create Global Temp Table
CREATE TABLE ##UserTempTable(
UserID int,
UserName varchar(250), 
Email varchar(250))

--Insert rows into Global Temp Table
Insert into ##UserTempTable values(1,'Morgan','Moragn@MorganTechSpace.com')
Insert into ##UserTempTable values(2,'Tim','Tim@MorganTechSpace.com')
Insert into ##UserTempTable values(2,'Shiv','Shiv@MorganTechSpace.com')

--Select rows from Global Temp Table
Select * from ##UserTempTable
Both Temporary tables are created in tempdb. Whenever we create new temp table, we can see it under Temporary folder of tempdb database.

Create Local temp table in SQL Server

Thanks,
Morgan
Software Developer

Saturday, 5 July 2014

The DELETE statement conflicted with the REFERENCE constraint

You will get this error when you are trying to Delete the record from a Table which has a reference in another Table. Consider two tables Customers(Primary Table) and SalesHistory(Relative Table).
Fix: The DELETE statement conflicted with the REFERENCE constraint
Here the column CustomerID is Primary Key of the table Customers and which is referenced as Foreign Key column in SalesHistoryTable. IDREFERENCE constraint is "FK__SalesHist__Custo__060DEAE8".

Fix: The DELETE statement conflicted with the REFERENCE constraint

When we try to delete a row from Customers Table with CustomerID which is referenced in SalesHistory.
Delete
  FROM [MorganDB].[dbo].[Customers] where CustomerID=2
We will get this SQL error:
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK__SalesHist__Custo__060DEAE8". 
The conflict occurred in database "MorganDB", table "dbo.SalesHistory", column 'CustomerID'.
To solve this issue, we need to delete corresponding rows from SalesHistory Table first and then delete from Table Customers.(it means, we need to delete dependency first before deleting actual data)
Delete
  FROM [MorganDB].[dbo].[SalesHistory] where CustomerID=2
Delete
  FROM [MorganDB].[dbo].[Customers] where CustomerID=2