Temporary tables, often known as temp tables, are widely used by database managers and developers. They function similarly to ordinary tables and are kept in the tempDB database, enabling you to choose, add, and remove data as needed. In the event that they are produced during a stored procedure, they will be removed once the operation is finished.

What does a SQL Server temporary table mean?
In SQL, a temporary table is a database table that is only temporarily present on the database server. For a set amount of time, a temporary table retains some of the data from a regular table.

Temporary tables are useful when you need to regularly work with a small subset of the many records in a table. Sometimes it is not necessary to filter the data several times in order to obtain the subset; instead, you can filter the data only once and save it in a temporary table.

Temporary tables and permanent tables are almost the same. They are created in TempDB and deleted right away after the last connection to the query window that created the table is closed. Temporary Tables can be used to process and store interim findings. Temporary tables are used when it is necessary to store data temporarily.

How to Create a Temporary SQL Table?
CREATE TABLE #tmpEmp
(
  Id INT,
  Name NVARCHAR(50),
  Rank INT
)

Types of Temporary Tables in SQL
There are a couple of temporary tables in SQL.

• Local Temporary Tables
• Global Temporary Tables

Local Temporary Tables
TempDB contains local temporary table storage. They are destroyed automatically at the conclusion of the operation or session, and they are only available to the one who created them.

For instance, after a local temporary table called #tmpEmp is created, the user's ability to manipulate the table is limited until the query window's final connection is closed. They can be recognized by the prefix #, such as #table name, and the same temporary table can be created with the same name in many windows.

A local temporary table can be created with the CREATE TABLE command, where the table name is prefixed with a single number sign (#table name).

Syntax
The following is the syntax in SQL Server (Transact-SQL) for making a LOCAL TEMPORARY TABLE.
CREATE TABLE #tablename
(
  column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    column3 datatype [ NULL | NOT NULL ],
    ...
    columnn datatype [ NULL | NOT NULL ]
);

Let’s insert some data entry in a temporary table.

INSERT INTO #tmpEmp ([Name], [Rank])
VALUES ('Peter Scott', 196)

Let’s check the results, whether it’s stored or not.
SELECT Id, [Name], [Rank] FROM #tmpEmp

Where do I store the temporary table on the SQL Server?

Another way to create a temporary table in SQL is with the SELECT INTO statement. For the illustration, go through the following things.
SELECT Id, [Name], [Rank]
INTO #tmpEmp1
FROM #tmpEmp

Temporary tables are useful when you need to regularly work with a small subset of the many records in a table. Sometimes it is not necessary to filter the data several times in order to obtain the subset; instead, you can filter the data only once and save it in a temporary table.

Temporary tables and permanent tables are almost the same. They are created in TempDB and deleted right away after the last connection to the query window that created the table is closed. Temporary Tables can be used to process and store interim findings. Temporary tables are used when it is necessary to store data temporarily.

Global Temporary Table in SQL Server
Additionally, they are kept in tempDB. These tables belong to the category of transient tables that are concurrently accessible to all users and sessions. When the final session using the temporary table concludes, they are automatically removed. These tables do not exist in the system catalogs and are not persistent.

A global temporary table is created with the CREATE TABLE command, and the table name is preceded by a double number sign (##table name).

Syntax
The following is the syntax in SQL Server (Transact-SQL) for making a global temporary table.
CREATE TABLE ##tablename
(
  column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    column3 datatype [ NULL | NOT NULL ],
    ...
    columnn datatype [ NULL | NOT NULL ]
);

Create a global temporary table.

CREATE TABLE ##tmpEmployee
(
  Id INT NOT NULL IDENTITY (1, 1),
  [Name] NVARCHAR(50),
  [Rank] INT
)

Let’s insert some data entry in a global temporary table.

INSERT INTO ##tmpEmployee ([Name], [Rank])
VALUES ('Peter', 196)
, ('Daniel', 1211)
, ('Maria', 1250)
, ('Laura', 1280)

Let’s check the results, whether it’s stored or not.
SELECT Id, [Name], [Rank] FROM ##tmpEmployee

Where do I store the temporary table on the SQL Server?

Delete Temporary and Global Temporary SQL Table
When possible, we should directly remove temporary tables rather than waiting for them to be deleted automatically when the connection is closed. in order to expeditiously release the temp resources.

Syntax
DROP TABLE TableName

As an illustration,
DROP TABLE #tmpEmp, #tmpEmp1, ##tmpEmployee

Let's check in the Temporary Tables from SQL Server.

HostForLIFEASP.NET SQL Server 2022 Hosting

There are two methods for determining whether a SQL table is temporal: Examine the table definition. There are two system-versioned period entries in temporal tables: sys_start_time and sys_end_time. These columns include the beginning and end dates of the time period for which the row is valid. A table with these two columns is a temporal table.

The OBJECTPROPERTY() function should be used. The OBJECTPROPERTY() function returns information about a database object, such as whether it is a temporal table. To determine whether a table is temporal, use the SQL statement:

SELECT OBJECTPROPERTY(object_id('table_name'), 'IsTemporalTable');

If the query returns a value of 1, the table is temporal. Otherwise, the table is not chronological. For instance, the following SQL statement will determine whether the Products table is temporal:

SELECT OBJECTPROPERTY(object_id('Products'), 'IsTemporalTable');

If the result of the query is 1, then the Products table is temporal. Otherwise, the Products table is not temporal.

Note: Temporal tables are only supported in Microsoft SQL Server 2016 and later versions.

Here are some additional things to keep in mind about temporal tables:

• Temporal tables can be either system-versioned or history-table versioned. System-versioned temporal tables are managed by the database engine, while history-table versioned temporal tables are managed by the user.
• Temporal tables can be used to store both current and historical data. Current data is stored in the table, while historical data is stored in a history table.
• Temporal tables can be used to query both current and historical data. The FOR SYSTEM_TIME clause can be used to specify the time period for which the data should be returned.

HostForLIFEASP.NET SQL Server 2022 Hosting

In this blog, we learn how to calculate credit debit Transactions like banking report using SQL Sever.

Step 1:  Create Table valued function for calculate credit, debit amount with total balance
USE [SqlBank]

CREATE   FUNCTION [dbo].[FNGetTransaction](@CID BIGINT)
RETURNS @Tab_TRansaction TABLE (id BIGINT ,CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200)
, CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime,Remarks varchar(max) , IsmailSend int)
AS
BEGIN

DECLARE @TempAC_ID BIGINT;

SET @TempAC_ID = (SELECT TOP 1 A.AC_ID FROM Tbl_Account A join Tbl_Cust
  C ON A.CID=C.CID WHERE c.CID=@CID)

DECLARE @Tbl_Tran Table
(id BIGINT,
CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200),
 CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime  ,
Remarks varchar(max)  , IsmailSend int
)

INSERT INTO @Tbl_Tran(id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate,Remarks,IsmailSend)
SELECT TR.TR_ID, CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END CreditAmt,
CASE WHEN tr.TR_CrDrType ='Dr' THEN tr.TR_Amt ELSE 0 END DebitAmt ,Tr.TR_Type,tr.TR_Date,Tr.AC_ID ,
 CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END - CASE WHEN tr.TR_CrDrType ='Dr'
 THEN tr.TR_Amt ELSE 0 END  Balance,
 Tr.TR_CrDrType  ,C.CName ,Acc.AC_NO ,C.CAddress ,C.CMObile,C.CEmail ,Acc.AC_OpDate ,
 Tr.Remarks , Tr.IsmailSend

FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc with(nolock) ON acc.AC_ID=Tr.AC_ID
      join Tbl_Cust C with(nolock) ON C.CID=Acc.CID
WHERE Acc.CID=@CID;

WITH Tbl_CTE_Tran
as
(
SELECT T2.id,T2.CreditAmt,T2.DebitAmt,SUM(T1.CreditAmt-T1.DebitAmt) Balance,
T2.Tr_Type,T2.TranDate,T2.AC_ID
,T2.TType,T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate,t2.Remarks,t2.IsmailSend FROM @Tbl_Tran T1
join @Tbl_Tran T2 on T1.id<=T2.id WHERE T2.AC_ID=@TempAC_ID
GROUP BY T2.id,T2.CreditAmt,T2.DebitAmt,T2.Tr_Type,T2.TranDate,T2.AC_ID,T2.TType,
T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate  ,t2.Remarks ,t2.IsmailSend
)

INSERT INTO @Tab_TRansaction (id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks ,IsmailSend
 )
SELECT id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType  ,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks,IsmailSend
FROM Tbl_CTE_Tran  with(nolock)
WHERE AC_ID=@TempAC_ID

RETURN
END

Step 2: Create Procedure & Call above function in Procedure

USE [SqlBank]

CREATE PROC [dbo].[PROC_TRansaction]
(
@TR_ID int=null output,
@CID bigint=null,
@TR_Amt decimal(18,2)=null,
@AC_ID bigint =null,
@Flag varchar(100)=null,
@AC_No bigint=null,
@Remarks varchar(max)=null,
@MTR_ID int=null output,
@Balance decimal(18,2)=null output
)
AS
BEGIN
DECLARE @TempTRAmount decimal(18,2)
DECLARE @Temp_ACID bigint
DECLARE @Tran_ScopID bigint;
DECLARE @Tran_ID bigint;
DECLARE @MMTR_ID bigint;

BEGIN TRAN Tbl_Transaction_Tran
   BEGIN  TRY
      IF(@Flag = 'Tran')
      BEGIN

IF EXISTS(SELECT 1 FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc
with(nolock) ON acc.AC_ID=Tr.AC_ID WHERE Acc.CID=@CID)
BEGIN
 SELECT  a.id id ,a.DebitAmt,a.CreditAmt,a.Balance
 ,a.Tr_Type, isnull(Format(a.TranDate,'dd-MMM-yyyy HH:mm'),'') TranDate, NCHAR(8377) Rupees ,a.TType,a.Remarks
   FROM dbo.FNGetTransaction(@CID) a
--      JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
--GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType
END
ELSE
BEGIN
Select 'No Transaction summary found...?' OpMsg
END
    END
ELSE IF(@Flag = 'IN')
    BEGIN
    SET @Temp_ACID = (SELECT Top 1 A.AC_ID  FROM Tbl_Account A with(nolock)
     Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE A.AC_No=@AC_No)
    DECLARE @SenderName varchar(max)
        SET @SenderName = (SELECT Top 1 c.CName  FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE c.CID=@CID)
    DECLARE @ReciverName varchar(max)
       SET @ReciverName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID
       WHERE A.AC_No=@AC_No)
SET @TempTRAmount = (
 SELECT TOP 1 ISNULL(SUM(b.balance),0) Balance
   FROM dbo.FNGetTransaction(@CID) a
JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType ORDER BY a.id desc)
if(@TR_Amt > @TempTRAmount)
BEGIN
Select 'Insuffitient Balance' as msg
END
ELSE
  BEGIN
  Declare @FixScratchAmt decimal(18,2)=500;
  --if not exists (select 1 from Tbl_Transaction Where TR_Date=CURRENT_TIMESTAMP and Ref_TranACC=@AC_ID)
  --begin
  Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Online - Transfer To - '+ @ReciverName + ' '+Cast(@Ac_NO as varchar(max))+' ',
  ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@AC_ID,'Dr','Tran-' +CAST(@AC_ID as varchar(max)),0,'S',0,@Remarks)
  set @Tran_ID = @@IDENTITY;
  set @TR_ID= @Tran_ID;
  set @Tran_ScopID= SCOPE_IDENTITY();
  Set @Balance = (SELECT TOP 1 BALANCE FROM dbo.FNGetTransaction(@CID) order by id desc)
  if(@TR_Amt >= @FixScratchAmt)
  begin
   Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Cash Back From S Bank7 ',10,CURRENT_TIMESTAMP,@AC_ID,'Cr',0,1,'R',0,'Cash back from Sbank7. Pay & win more cash back ')
  END

Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Recived From ' + @SenderName + ' Tran - '+Cast(@Tran_ScopID as varchar(max))+'-'+
  CAST(@AC_ID as varchar(max)),ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@Temp_ACID,'Cr','Tran-'
  +Cast(@Tran_ScopID as varchar(max))+'-'+ CAST(@AC_ID as varchar(max)),0,'R',0,@Remarks)
  set @MMTR_ID = @@IDENTITY;
  set @MTR_ID = @MMTR_ID;
    END
    END
IF(@@TRANCOUNT > 0)
  BEGIN
  COmmit tran Tbl_Transaction_Tran
  END
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
            BEGIN
            ROLLBACK TRAN Tbl_Transaction_Tran
            END
            DECLARE @USERID varchar(max),@ERRORLINE varchar(max)
            ,@ERRORMESSAGE varchar(max),@ERRORPROCEDURE varchar(500),@ERRORSEVERITY varchar(max)
            ,@ERRORSTATE varchar(max), @ErroFrm varchar(max)

            SELECT @USERID = SUSER_SNAME(),@ERRORLINE=ERROR_LINE(),@ERRORMESSAGE=ERROR_MESSAGE(),
                   @ERRORPROCEDURE=ERROR_PROCEDURE(),@ERRORSEVERITY=ERROR_SEVERITY(),
                  @ERRORSTATE= ERROR_STATE() ,@ErroFrm = 'Backend'

        EXEC Proc_ERRORLOG @USERID,@ERRORLINE,@ERRORMESSAGE,@ERRORPROCEDURE,@ERRORSEVERITY,@ERRORSTATE,0,@ErroFrm
END CATCH
END

Step 3: Execute Procedure to Check Report
exec [dbo].[PROC_TRansaction]

@CID =2,@Flag='Tran'

I hope it works!

HostForLIFEASP.NET SQL Server 2019 Hosting

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

• Data files or log files are corrupt.
• Database server was shut down improperly
• Lack of Disk Space
• SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

• Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

• Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

• Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

• For safety, take the backup of the database.
• Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
• There is no way to go back to the previous state of the database.
• So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

• Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

• Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.