SQL

(Home)
Top 3 Resources 
Resource Introduction to Structured Query Language
Resource SQL-Server-Performance.com
Resource SQL Server Issues
Resource ExtremeExperts
Quick Referencek Reference
MAX NUMBER OF CHARS/NTEXT SELECT DATALENGTH(notes) FROM tblNotes WHERE ...
CREATE TABLE and INSERT

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_patients_Filter1')
DROP TABLE new_patients_Filter1
GO
USE protracker_test
CREATE TABLE new_patients_Filter1
(
ID int,
pt_name_l nvarchar(40),
pt_name_f nvarchar(40),
pt_dob datetime
)
INSERT INTO new_patients_Filter1
SELECT max(distinct pt_ID) as ID, pt_name_l, pt_name_f, pt_dob
FROM tblHlab_Patients_org
GROUP BY pt_name_l, pt_name_f, pt_dob
HAVING pt_dob IS NOT NULL AND pt_dob <> '1/1/1900'
ORDER BY ID ASC
INSERT INSERT INTO "table" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)
INSERT MULTIPLE ROWS INSERT INTO "table_1" ("column_1", "column_2", ...)
SELECT "column_3", "column_4",  etc.
FROM "table_2"
DELETE 
DELETE FROM table

WHERE columnName = someValue
UPDATE 
UPDATE table

SET columnName = newValue

WHERE columnName = someValue
BETWEEN Select Modern_Last_Name, Modern_First_Name From Modern_Table Where Modern_Last_Name between "Smith" and "Stothers"
  Select Festival_Year, Festival_Title From Festival_Table Where Festival_Year between "2000" and "2004" and Festival_Title in (Select Festival_Title From Modern_Festival_Table Where Modern_Last_Name = "Smith" and Modern_First_Name = "Bill")
  
SELECT * FROM Employees WHERE LastName 

NOT BETWEEN 'Barnes' AND 'Noble'
  
SELECT columnName FROM table

WHERE columnName

BETWEEN value_1 AND value_2
HAVING SELECT "columnName1", SUM("columnName2")
FROM "table"
GROUP BY "columnName1"
HAVING (math function)
COUNT SELECT COUNT("columnName")
FROM "table"
NO COUNT  
SUBQUERY SELECT "columnName1"
FROM "table"
WHERE "columnName2" [Comparison Operator]
(SELECT "columnName1"
FROM "table"
WHERE [Condition])
TOP  
INNER JOIN  
NATURAL JOIN SELECT B1.area_name REGION, SUM(B2.Sales) SALES
FROM Geography B1, Store_Information B2
WHERE B1.store_name = B2.store_name
GROUP BY B1.area_name
OUTER JOIN SELECT B1.bldg_name, SUM(B2.Sales) SALES
FROM Georgraphy B1, Bldg_Information B2
WHERE B1.bldg_name = B2.bldg_name (+)
GROUP BY B1.bldg_name
RETVAL  
ORDER BY SELECT "columnName"
FROM "table"
[WHERE "condition"]
ORDER BY "columnName" [ASC, DESC]
REFERENCE ANOTHER OBJECT  
GROUP BY SELECT "columnName1", SUM("columnName2")
FROM "table"
GROUP BY "columnName1"
WHERE SELECT "columnName"
FROM "table"
WHERE "condition"
DROP  
ALTER TABLE  
LIKE SELECT "columnName"
FROM "table"
WHERE "columnName" LIKE {PATTERN}
  SELECT *
FROM Bldg_Information
WHERE bldg_name LIKE '%BD%'
LIKE (time) SELECT so_reorder_print_date FROM dbo.tblStandingOrderItems 
WHERE (so_reorder_print_date LIKE CONVERT(DATETIME,'" & killDate & "', 102))
AND  
OR SELECT "columnName"
FROM "table"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
  SELECT bldg_name
FROM Bldg_Information
WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)
JOIN 2 DATABASES Create a table in NorthWind called tblTest, add 2 fields, first an id field called tbl_ID, second a description field called tbl_Description. Build this exact same table in Pubs as well!

Insert records into both tables and create records which you know will join up later on!

Code below:

SELECT Pubs.dbo.tblTest.tbl_Description, NorthWind.dbo.tblTest.tbl_Description FROM limage.NorthWind.dbo.tblTest 
JOIN PUBS.dbo.tblTest ON NorthWind.dbo.tblTest.tbl_Description = Pubs.dbo.tblTest.tbl_Description 
ORDER BY NorthWind.dbo.tblTest.tbl_Description
 
RETURN IDENTITY sqlStr = "SET NOCOUNT ON INSERT INTO tblProTracker_TrackID (trk_container, trk_user) VALUES ('" & container & "','" & user & "') SELECT @@IDENTITY as newID SET NOCOUNT OFF" rst.Open sqlStr, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
Declare and Set DECLARE @UserId nvarchar(10)
DECLARE @formName nvarchar(20)
DECLARE @password nvarchar(30)
DECLARE @Level int

set @UserId = 'reh'
set @formName = 'abiDataEntry'
set @password = '070F2'
set @Level = 1
SELECT Count(emp_t_User) FROM vew_UserFormAuthorization
where (emp_t_User = @UserId And fau_nl_Level >= @Level And emp_t_password = @password And fau_t_FormLogicName = @formName)
RETURN
 
ROUND ROUND(var1, 2) where 2 is number of decimals!
VERSION SELECT @@VER
VERSION SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'), SERVERPROPERTY ('edition')
UPDATE DATE AS NULL Alter PROCEDURE spMR_Update1 
@myID as int,
@mod_complete_date as NVARCHAR(27)
AS

UPDATE tblModRequest
SET mod_complete_date = Case @mod_complete_date
When '' Then null
Else @mod_complete_date
End
WHERE mod_Id = @myID

return
IDENTITY INSERT SET IDENTITY_INSERT dbo.Client ON

INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (782, 'Edgewood Solutions')

INSERT INTO dbo.CLIENT (ClientID, ClientName) VALUES (783, 'Microsoft')

SET IDENTITY_INSERT dbo.Client OFF

 
REPLACE Update Terminals
Set Name = replace(Name,Char(233),'e')
Where CharIndex(Char(233),Name) > 0
ISNULL (replace null with zero) SELECT [View CRL ID].Field1, ISNULL([View C 1].Total, '0') AS Jan,
   ISNULL([View C 4].Total, '0') AS April
FROM dbo.[View CRL ID] full OUTER JOIN
   dbo.[View C 4] ON
   dbo.[View CRL ID].Field1 = dbo.[View C 4].SrcFacCode full OUTER
    JOIN
   dbo.[View C 1] ON
   dbo.[View CRL ID].Field1 = dbo.[View C 1].SrcFacCode
GROUP BY dbo.[View CRL ID].Field1, dbo.[View C 1].Total,
   dbo.[View C 4].Total
DYNAMIC SQL COUNTER select rank=count(*), a1.au_lname, a1.au_fname
   from authors a1, authors a2
   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
   group by a1.au_lname, a1.au_fname
   order by rank

Use the following code in SQL Server 2005.

   select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
   from authors a
   order by rank 

 
SQL String Function Private Function Fill_JoinArray_Qtr()
       '**************************************************************
       'Array grid
       '**************************************************************
       'JoinArray(j, 0) = Month
       'JoinArray(j, 1) = Year
       'JoinArray(j, 2) = [Month-Year] ie [Mar-2006]  - P  - Column
Alias
       'JoinArray(j, 3) = [Month-Year] ie [Mar-06]         - Variance
Alias
       'JoinArray(j, 4) = Partial
       '**************************************************************
       ' Partial is attched to the column 2
       '**************************************************************

       '*************************************************
       ' Only month and year will matter not the DAY!!!!        
       '*************************************************


       Dim retStr As String
       Dim strConnection As String
       strConnection = ConfigurationSettings.AppSettings
("DB:Client_Trak")
       Dim conn As SqlConnection = New SqlConnection(strConnection)

       Dim strSQL As String
       Dim yearStart As Integer
       Dim yearStop As Integer
       Dim quarterStart As Integer
       Dim quarterStop As Integer
       Dim dateStart As Date
       Dim dateStop As Date
       Dim i, j As Integer
       Dim fg_start As Boolean
       Dim retQuarter As String
       Dim myDate As Date

       'initialize
       fg_start = False


       yearStart = getYearStart_Qtr()
       yearStop = getYearStop_Qtr()
       quarterStart = getQuarterStart_Qtr()
       quarterStop = getQuarterStart_Qtr()

       'yearStart = Year(Convert.ToDateTime(myDate))
       'yearStop = Year(Convert.ToDateTime(Left
(Me.cmb_date_stop.SelectedValue.ToString(), myPos - 1)))
       'monthStart = Month(Convert.ToDateTime(Right
(Me.cmb_date_start.SelectedValue.ToString(), Len
(Me.cmb_date_start.SelectedValue) - myPos)))
       'monthStop = Month(Convert.ToDateTime(Right
(Me.cmb_date_stop.SelectedValue.ToString(), Len
(Me.cmb_date_start.SelectedValue) - myPos)))

       'clean out array
       For i = 0 To 99
           JoinArray(i, 0) = ""
           JoinArray(i, 1) = ""
           JoinArray(i, 2) = ""
           JoinArray(i, 3) = ""
       Next i

       'SQL statement
       'strSQL = "SELECT Month(curdate) as Mon, dbo.getMonth(Month
(curDate)) as strMonth, curDate, partialMonth as prt "
       'strSQL = strSQL & "FROM tblClientLoss_main "
       'strSQL = strSQL & "GROUP BY curDate, partialMonth "
       'strSQL = strSQL & "HAVING (Year(curDate) >= 2006) "
       'strSQL = strSQL & "ORDER BY curDate"

       'strSQL = "Select dbo.CTgetQuarter(curDate) as myDate1 "
       'strSQL = strSQL & "FROM tblClientLoss_Main "
       'strSQL = strSQL & "GROUP BY dbo.CTgetQuarter(curDate) "
       'strSQL = strSQL & "ORDER BY dbo.CTgetQuarter(curDate)"


       strSQL = "Select Year(curDate) as myDate1 "
       strSQL = strSQL & "FROM tblClientLoss_Main "
       strSQL = strSQL & "GROUP BY curDate "
       strSQL = strSQL & "ORDER BY curDate"

       Dim myCommand As New SqlCommand(strSQL, conn)

       conn.Open()
       Dim myReader As SqlDataReader
       myReader = myCommand.ExecuteReader()
       i = -1
       j = -1
       While myReader.Read()
           If fg_start = False Then
               Response.Write(myReader.GetInt32(0).ToString())
               If myReader.GetInt32(0).ToString() = quarterStart Then
                   fg_start = True
               End If
           End If

           '************************************************
           ' FILL ARRAY
           '************************************************
           If fg_start = True Then
               j = j + 1
               'If date has some value
               If IsNumeric(myReader.GetInt32(0).ToString()) Then
                   retQuarter = getQuarterName(Month
(myReader.GetDateTime(2).ToString()))  'curdate
                   If Not (Year(myReader.GetDateTime(2).ToString()) =
yearStop And retQuarter = quarterStop) Then
                       'retMonth = getQuarterName(Month
(myReader.GetDateTime(2).ToString()))  'curdate
                       JoinArray(j, 0) = Month(myReader.GetDateTime
(2).ToString())     'curdate
                       JoinArray(j, 1) = Year(myReader.GetDateTime
(2).ToString())    'curdate                        
                       JoinArray(j, 4) = myReader.GetBoolean
(3).ToString()
                       'If partial false then
                       If myReader.GetBoolean(3).ToString() = False
Then
                           JoinArray(j, 2) = "[" & retQuarter & "-" &
Year(myReader.GetDateTime(2).ToString()) & "]"  'curdate
                       Else
                           JoinArray(j, 2) = "[" & retQuarter & "-" &
Year(myReader.GetDateTime(2).ToString()) & "-P]"  'curdate
                       End If
                       JoinArray(j, 3) = retQuarter & "-" & Right(Year
(myReader.GetDateTime(2).ToString()), 2)   'curdate
                   Else
                       'last record
                       'retMonth = getQuarterName(Month
(myReader.GetDateTime(2).ToString()))  'curdate
                       JoinArray(j, 0) = Month(myReader.GetDateTime
(2).ToString())     'curdate
                       JoinArray(j, 1) = Year(myReader.GetDateTime
(2).ToString())    'curdate
                       JoinArray(j, 4) = myReader.GetBoolean
(3).ToString()
                       'If partial false then
                       If myReader.GetBoolean(3).ToString() = False
Then
                           JoinArray(j, 2) = "[" & retQuarter & "-" &
Year(myReader.GetDateTime(2).ToString()) & "]"  'curdate
                       Else
                           JoinArray(j, 2) = "[" & retQuarter & "-" &
Year(myReader.GetDateTime(2).ToString()) & "-P]"  'curdate
                       End If
                       JoinArray(j, 3) = retQuarter & "-" & Right(Year
(myReader.GetDateTime(2).ToString()), 2)   'curdate
                       GoTo lbl_close
                   End If
               End If
           End If
       End While
       myReader.Close()
       conn.Close()

lbl_close:
       myReader.Close()
       conn.Close()

   End Function
CAST AND CONVERT

CAST

CAST ( expression AS data_type )

CONVERT

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Code