SQL SERVER - Important Query Part-2

1. COPYING WHOLE DATA OF A TABLE

SELECT * INTO TABLE_DESTINATION FROM TABLE_SOURCE

2. SELECT ONLY DATE PART FROM DATETIME – BEST PRACTICE

Just a week ago my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with date.

SELECT GETDATE()

ResuleSet : 2007-06-10 7:00:56.107

The required outcome was only 2007/06/10.

I asked him to come up with solution by using date functions. The method he suggested was to use

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

I approved his method though, I finally suggested my method using function CONVERT.

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed on MSDN.

Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.

3. QUERY FOR UPDATING IN THE EXISTING TABLE COMMON COLUMN IN ALL THE TABLES

EXEC SP_MSFOREACHTABLE'

DECLARE @TBLNAME VARCHAR(255);

SET @TBLNAME =  PARSENAME("?",1);

DECLARE @SQL NVARCHAR(1000);

IF EXISTS(

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 

 WHERE TABLE_NAME = @TBLNAME AND COLUMN_NAME = ''ISDELETED''

) 

BEGIN        

SET @SQL = N''ALTER TABLE '' +  @TBLNAME + N'' ALTER COLUMN ISDELETED BIT NOT NULL;''       

EXEC SP_EXECUTESQL @SQL

END'

_________________________________________________________________
LIKE Query

--Using Like in SQL Query
SELECT * FROM ADDRESS WHERE CITY LIKE '%NAINITAL%'

-- Like Query with a Parameter
@word AS VARCHAR = NAINITAL
SELECT * FROM ADDRESS WHERE CITY LIKE '%' + @word + '%'