Sql Server :: Cool Sqls

Some useful sqls I am sharing here.

Description Sql
Get Row Number of any table
SELECT ROW_NUMBER() OVER (ORDER BY Name ASC) AS ROWID, * FROM  TableX
Delete Duplicate Records

WITH CTE (Col1,Col2, DuplicateCount)

AS

(

SELECT Col1,Col2,

ROW_NUMBER() OVER(PARTITION BY Col1,Col2 ORDER BY Col1,Col2) AS DuplicateCount

FROM Table1

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

Create Copy of a table

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

Create Linked Server

exec sp_addlinkedserver @server = ‘servername‘, @srvproduct = ”, @provider = ‘sqloledb’,

@datasrc = ‘serverhostname‘, @catalog = ‘dbname

Copy table from linked server to local server SELECT * INTO NewTable FROM RS.Db.dbo.RemoteTable
Transactions

BEGIN TRAN;

    SELECT * FROM Address

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    COMMIT TRAN;

    RETURN;

ERR_HANDLER:

PRINT ‘Unexpected error occurred!’

ROLLBACK TRAN

Identity Insert Enable SET IDENTITY_INSERT YourTable ON

Some Cool Sql Server Links

Insert Query Generator

http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *