Shadow Copy In SQL Server Without Using Create Command

Shadow Copy In SQL Server Without Using Create Command

Various methods of copying a table with a new name using a select statement in SQL Server. It takes a lots of time to create new empty table and defining all field names again. Various methods are explained below.

Creating Table in SQL Server

create table StudentDetails

(

roll_num int,

F_Name varchar(15),

L_Name varchar(15),

City varchar(15)

)

Insert values in above table and table will look like below image

Method I

Select all columns into new table but this way of copying will not copy constraints and indexes. This technique will copy all the columns of the source table (StudentDetails) to the destination table (StudentDetailsCopy).

Method II

Select only the columns we want into the new table. Suppose we want to copy roll_num, F_Name, City columns from StudentDetails table into the StudentDetailsCopy1 table. Constraints and indexes will not be copied.

Method III

To copy the structure of the source table into another new table with new name. Constraints and indexes will not be copied.

Here, 1=2 will prevent the data from being copied from StudentDetail to the StudentDetailCopy2 table.

Using try catch in SQL Server stored procedures with example

Using try catch in SQL Server stored procedures with example

To handle exceptions in SQL Server we can use TRY…… CATCH blocks. To use TRY…… CATCH blocks in stored procedure we need to write the query like as shown below

BEGIN TRY

---Write Your Code

END TRY

BEGIN CATCH

---Write Code to handle errors

END CATCH

In TRY block we will write our queries and in CATCH block we will write code to handle exceptions. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are

ERROR_LINE() - This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() - This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - This function will return state number of error message which cause to raise error.

ERROR_PROCEDURE() - This function will return name of the procedure where an error occurred.

ERROR_MESSAGE() - This function will return the complete text of the error message which cause to raise error.

Check below sample query to handle errors in stored procedure

CREATE PROCEDURE uspTryCatchTest

AS

BEGIN TRY

    SELECT 1/0

END TRY

BEGIN CATCH

    SELECT ERROR_NUMBER() AS ErrorNumber

     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState

     ,ERROR_PROCEDURE() AS ErrorProcedure

     ,ERROR_LINE() AS ErrorLine

     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

Output :

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure ErrorLine ErrorMessage

8134                  16        1                    NULL                     2 Divide by zero error encountered.