Category: sql

SQL Insert if not exists

April 4, 2016 /

This example shows how to insert a record in an SQL table only if it does not currently exist.

if not exists
 (SELECT * FROM TEST_Table
 WHERE Center = '8882'
 AND Drive = 'K' AND Path = '\\DEV2\WORKGROUP')
 INSERT INTO TEST_Table (Center, Drive, Path, Comment)
 VALUES ('8882','K','\\DEV2\WORKGROUP','Stage Layout')

SQLite syntax:

INSERT OR REPLACE INTO topics (tag, topic) 
SELECT 'GEN:03', 'THE FALL'
WHERE NOT EXISTS 
 (SELECT * FROM topics WHERE tag = 'GEN:03' AND topic = 'THE FALL');

SQL Conditional Record Update

February 4, 2015 /

A Visual Basic example on how to update a record if it exists or insert it if it doesn’t.

Sub RecordStandardizedComputer(WS As String)
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim xSQL As String

 txt3 = "SELECT ComputerID from LOG_ComputersStandardized" _
 & " where ComputerName = " & WS

 txt2 = "INSERT INTO LOG_ComputersStandardized (ComputerName, RunTime)" _
 & "VALUES (" & WS & "','" & Now.ToString & "')"

 txt1 = "UPDATE LOG_ComputersStandardized SET " _
   & " DateTime = '" & Now.ToString & "'" _
   & " WHERE ComputerName = '" & WS & "'"

 xSQL = "IF NOT EXISTS (" & txt3 & ")" & txt2 & " ELSE " & txt1
  ExecuteSqlCommand(xSQL)
  End Sub

SQL Date Range Examples

September 12, 2013 /

Today

SELECT EventTime, Event, AppTitle, AppVersion
FROM LOG_AutomatedApplicationRunTime
WHERE EventTime >= DATEADD(d, 0, DATEDIFF(d, 0, GetDate()))
ORDER BY EventTime

Past 30 Days

SELECT EventTime, Event, AppTitle, AppVersion
FROM LOG_AutomatedApplicationRunTime
WHERE EventTime < DATEADD(day, – 30, GETDATE())
ORDER BY EventTime

SQL Basic Command Syntax

September 12, 2013 /
I use SQL statements on an almost daily basis, yet I have trouble remembering the syntax of even the most basic commands.  Thus I decided to post a cheat sheet here for quick and easy reference.  I hope you may find it valuable.
Selecting Records SELECT * FROM Table WHERE Param1 < 10 ORDER BY Param2
Selecting Records Into a New Table SELECT * INTO newdb.dbo.newtable FROM olddb.dbo.oldtable
Updating Records UPDATE Table SET Param1 = ‘cat’, Param3 = ‘fish’ WHERE Param2 = ‘dog’
Adding Records INSERT INTO Table (Param1, Param2) VALUES (‘xx’, ‘yy’)
Deleting Records DELETE FROM Table WHERE Param1 = ‘value’
Group By SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name = value GROUP BY column_name
List all tables & views in database SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_TYPE, TABLE_NAME

SQL Studio: Clear Server List

September 12, 2013 /

Over time, SQL Server Management Studio’s Server Dropdown box gets cluttered with many servers.  Quite often some of the servers no longer exist.  However, SQL Studio does not give the user an option for clearing this list.  I found this to be very frustrating.  The instructions below show how this can be done.

I tested these instructions with SQL Studion 2008 running on Windows XP and Windows 7.

SQL Server 2008 running on Windows XP:

  1. Close SQL Studio if it is open
  2. Go to C:\Documents and Settings\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell
  3. Delete the file SqlStudio.bin
    The file will be recreated automatically when SQL Studio starts.

—

SQL Server 2008 running on Windows 7:

  1. Close SQL Studio if it is open
  2. Go to C:\Users\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools
  3. Delete the file SqlStudio.bin
    The file will be recreated automatically when SQL Studio starts.

—

Special thanks to Dave Pinal at http://www.SQLAuthority.com for providing this information.