Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

    Advertisements
  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand
  • Advertisements

Sql Server :: Cool Sqls

Posted by Paul on November 11, 2010


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s