Menu Close

SQL Queries

SQL Queries

How to Find Duplicate Values

 

select transno,count(0) AS `COUNT( * )` from `1stupayment` where (`txtmsg` = ‘Success’) group by `transno` having (count(0) > 1) 

Find Duplicate Rows

SELECT *
    FROM [SampleDB].[dbo].[Employee]
    WHERE ID NOT IN
    (
        SELECT MAX(ID)
        FROM [SampleDB].[dbo].[Employee]
        GROUP BY [FirstName],
                 [LastName],
                 [Country]
    );

Delete Duplicate Rows

DELETE FROM [SampleDB].[dbo].[Employee]
    WHERE ID NOT IN
    (
        SELECT MAX(ID) AS MaxRecordID
        FROM [SampleDB].[dbo].[Employee]
        GROUP BY [FirstName],
                 [LastName],
                 [Country]
    );

Clone of a Table with new table

SELECT * INTO <new table name> FROM <old table name>;

Copy all columns from one table to another table

INSERT INTO table2 SELECT * FROM table1 WHERE condition;

Copy selected columns from one table to another table

INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

Instance Creation from one SQL server to Another Server

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver16

EXEC sp_addlinkedserver
@server=N’inst82′,
@srvproduct=N”,
@provider=N’SQLNCLI’,
@datasrc=N’P82SRV1\SQLExpress’;




EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’inst82′,
@locallogin = NULL,
@useself = N’False’,
@rmtuser = N’sa’,
@rmtpassword = N’rst.com’


select * from [inst82].texzone82.dbo.dispatch order by dispatchdate desc