SQL statements – SELECT

How to know number of rows being affected by SELECT/INSERT/UPDATE…?

  • SQL Server[sql]
    UPDATE Employee
    SET JobTitle = N’Executive’
    WHERE NationalIDNumber = 123456789

    IF @@ROWCOUNT = 0
    PRINT ‘Warning: No rows were updated’;
    [/sql]

    [sql]
    SELECT * FROM Employee

    IF @@ROWCOUNT = 0
    PRINT ‘Warning: Employee table is empty’;
    [/sql]

  • @@ROWCOUNT only affects to the query right above it

How to see query history?

  • SQL Server Management Studio[sql]use <db_name>
    SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
    CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
    ORDER BY execquery.last_execution_time DESC[/sql]

How to safely delete row so you can rollback

  • SQL Server[sql]BEGIN TRANSACTION;
    DELETE ___ WHERE ___
    — COMMIT TRANSACTION; — Run this if you want your “delete” being applied
    — ROLLBACK TRANSACTION; — Run this if you want to ignore your “delete”
    [/sql]

How to apply “try/catch” in query?

  • SQL Server[sql]Begin try
    BEGIN TRANSACTION;
    DELETE___ WHERE___
    End try
    Begin catch
    if @@error <> 0 goto err
    else
    begin
    COMMIT TRANSACTION;
    end
    End catch

    err:
    ROLLBACK
    [/sql]

How to perform a loop statement?

  • SQL Server – Use cursor[sql]DECLARE a_certain_cursor CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT field_1, field_2, field_3 FROM table WHERE id = condition

    OPEN a_certain_cursor

    FETCH NEXT FROM a_certain_cursor INTO @var_1, @var_2, @var_3
    WHILE @@FETCH_STATUS = 0
    begin
    — Do something
    end
    CLOSE a_certain_cursor
    DEALLOCATE a_certain_cursor
    [/sql]

  • SQL Server – Use “WHILE” command[sql]
    DECLARE @LoopCounter INT = 1, @totalRecords INT = 0
    SELECT @totalRecords = COUNT(id) FROM #tmp_tbl
    WHILE(@LoopCounter <= @totalRecords)BEGIN
    — Do something
    — Reset loop
    SET @LoopCounter = @LoopCounter + 1
    END
    [/sql]
  • “WHILE” is more efficient than “CURSOR” because if data is too big, “out of memory” can be the issue for “CURSOR” usage

How to backup a table?

  • SQL server[sql]select * into <backup_table_name> from <table_name>[/sql]

    No need to create table ahead

How to create temporary table?

  • SQL Server[sql]CREATE table #tmp_tbl(
    id int not null IDENTITY(1,1) PRIMARY KEY ,
    value nvarchar(max) ,
    value2 nvarchar(max) null
    )[/sql]

    [sql]DECLARE @tmp_tbl table(
    id int not null IDENTITY(1,1) PRIMARY KEY ,
    value nvarchar(max) ,
    value2 nvarchar(max) null
    )[/sql]

How to check if a table exists or not?

How to see temporary table structure?

  • SQL Server[sql]exec tempdb..sp_help ‘#myTempTable'[/sql]

How to select data?

How to select data by UNION using the common WHERE condition?

  • Example[sql]SELECT * FROM (
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id
    UNION
    SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id
    ) A
    WHERE EnrollDate < ‘2010-01-01’
    ORDER BY EnrollDate[/sql]

How to select day of week from a DATETIME?

  • Example:[sql]SELECT DATEPART(dw,’2017-07-23 00:00:00′)
    — 1 Sunday
    — 2 Monday
    — 3 Tuesday
    — 4 Wednesday
    — 5 Thursday
    — 6 Friday
    — 7 Saturday
    [/sql]

How to get date difference between two DATETIME?

  • Example[sql]select datediff(dd, ‘2017-01-01 00:00:00’, ‘2017-01-04 00:00:00’)
    — 3

    select datediff(dd, ‘2017-01-01 00:00:00’, ‘2017-01-05 00:00:00’)
    –4
    [/sql]

How to work with “working day” excluding weekend?

  • SQL Server[sql]WITH AllDates AS
    ( SELECT TOP (DATEDIFF(DAY, ‘Jan 01 2017’, ‘Jan 31 2017’))
    D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), ‘Jan 01 2017’)
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b )

    SELECT * FROM AllDates WHERE DATEPART(WEEKDAY, D) NOT IN (6, 7)[/sql]

How to combine values having same ID?

  • SQL Server[sql]
    SELECT p1.ID,
    (SELECT logic + ‘_DELIMITER_’ FROM #temp2 p2 WHERE p2.ID = p1.ID FOR XML PATH(”) ) AS after_combine
    FROM #temp1 p1
    GROUP BY ID[/sql]

How to select a result into string with a delimiter?

  • SQL Server[sql]
    — Preparation
    CREATE TABLE tblUsers
    ([name] varchar(7))
    ;

    INSERT INTO tblUsers
    ([name])
    VALUES
    (‘asieh’),
    (‘amir’),
    (‘safoora’)
    ;
    [/sql]

    [sql]
    SELECT STUFF((
    select ‘,’+ name
    from tblUsers
    FOR XML PATH(”)
    )
    ,1,1,”) AS names

    —— Results ——
    –NAMES
    –asieh,amir,safoora
    [/sql]

How to find duplicate values?

  • SQL Server
    [sql]
    — Find all email address appear more than 1
    SELECT email, COUNT(email) AS NumOccurrences
    FROM users GROUP BY email
    HAVING ( COUNT(email) > 1 )
    [/sql]

How to find records which don’t exist in another table?

  • SQL Server
    [sql]
    — Avoid using “not in”
    select * from TABLE01 where email_address not in (SELECT email_address from TABLE02)
    [/sql]
    [sql]
    SELECT *
    FROM TABLE01 tbl01
    WHERE NOT EXISTS
    (SELECT *
    FROM TABLE02 tbl02
    WHERE tbl02.email_address = tbl01.email_address)
    [/sql]
    [sql]
    SELECT *
    FROM TABLE01 tbl01
    LEFT JOIN TABLE02 tbl02 ON (tbl02.email_address = tbl01.email_address)
    WHERE tbl02.email_address IS NULL
    [/sql]

How to find values NOT match a table row

    [sql]
    SELECT * FROM
    (values (1),(2),(3),(4)) as T(ID)
    EXCEPT
    SELECT ID
    FROM [TABLE];
    [/sql]
    or
    [sql]
    SELECT * FROM
    (values (‘aaa’), (‘bbb’), (‘ccc’)) as T(elementName)
    EXCEPT
    SELECT elementName
    FROM ElementNames;
    [/sql]

How to work with string?

How to replace text in a string?

  • SQL Server[sql]SET @string= REPLACE(@string, ‘<‘, ‘>’)[/sql]

How to get position of text inside a string?

  • SQL Server[sql]SELECT CHARINDEX(‘keyword ‘, @string)[/sql]

How to remove text inside a string?

  • SQL Server
    STUFF deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position[sql]STUFF ( <character_expression>, <start>, <length>, <character_expression> )[/sql]

How to loop string with delimiter of comma?

[sql]
DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = ‘aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,’

set @pos = 0
set @len = 0

WHILE CHARINDEX(‘,’, @valueList, @pos+1)>0
BEGIN
set @len = CHARINDEX(‘,’, @valueList, @pos+1) – @pos
set @value = SUBSTRING(@valueList, @pos, @len)
–SELECT @pos, @len, @value /*this is here for debugging*/

PRINT @value
–Here is you value
–DO YOUR STUFF HERE
–DO YOUR STUFF HERE
–DO YOUR STUFF HERE
–DO YOUR STUFF HERE
–DO YOUR STUFF HERE

set @pos = CHARINDEX(‘,’, @valueList, @pos+@len) +1
END
[/sql]

Be the first to comment

Leave a Reply

Your email address will not be published.


*