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]