select correct email formats only

SELECT *

FROM EmailTest

WHERE

(

CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0

AND LEFT(LTRIM([Email]),1) <> '@'

AND RIGHT(RTRIM([Email]),1) <> '.'

AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1

AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1

AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3

AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0)

)

copy tables from one database to another

If you have a table in a database and you would like to copy the table to another database, use this query:

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

Update and Insert data in one table with data from another table

-- To Update data from one table to another table

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40


-- To Insert data from one table to another table

INSERT INTO TestTable (FirstName, LastName)
   SELECT FirstName, LastName
   FROM Person.Contact
   WHERE EmailPromotion = 2

Adding Identity field dynamically to a sql table

To add an identity field when a table has no identity field use

Select Row_Number() over (order by [any Column name]) as Id,[Col1],col2,..... from [Table Name]

Find and Clear Duplicates from sql table

To Find duplicates:

select [col1],[col2] from [TableName] where [condition]
group by [col1],[col2],[col3],[col4] having count(*) > 1


Example:

select CompanyName, State from registerinfo where RegisterinfoId >12000
group by Companyname, State, Add1, City having count(*) > 1


To Clear Duplicates:

SELECT *
FROM registerinfo
WHERE registerinfoid IN
(
SELECT registerinfoid
FROM registerinfo
WHERE EXISTS
(
SELECT NULL
FROM registerinfo AS tmptable
WHERE registerinfo.Companyname = tmptable.companyname and
registerinfo.add1 = tmptable.add1 and
registerinfo.State = tmptable.state
HAVING
registerinfo.registerinfoid > MIN(tmptable.registerinfoid)
)
)

Drop all the tables from a database

To delete all the tables from a database:

Select 'drop table ' + Object_name(object_id) from sys.objects where type = 'U'

(or)

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"



To Delete All Records from a table:

Select 'delete from ' + Object_name(object_id) from sys.objects where type = 'U'