SQL

@@Error

UPDATE LJ_Enrollment
SET
 LawJournalPackage = @LawJournalPackage,
 LawJournalExpireDate = @LawJournalExpireDate
WHERE LJ_Email = @Email

If @@Error <> 0
 Begin
  Set @OrderID = 0
 End
Else
 Begin
  Set @OrderID = @@Identity
 End

Bulk Copy Program (BCP)

SQL Express does not provide an Import/Export wizard. The BCP Utility allows you to do this through command line.

-- Export
bcp pubs.dbo.authors out c:\temp\authors.bcp

-- Import
bcp BKMulti.dbo.tbl_Member in C:\Websites\BKMulti\IMPORT\BKM_Students.txt -T

Bulk Insert

BULK INSERT pubs..publishers2 
FROM 'c:\newpubs.txt'
WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

Case When

-- Pass in @col param value as one of 3 values
DECLARE @col VARCHAR(9) 
SET @col = 'firstname' 
 
IF @col IN ('firstname', 'email', 'salary') 
    SELECT * FROM blat 
        ORDER BY CASE @col 
            WHEN 'firstname' THEN firstname 
            WHEN 'email' THEN email 
            WHEN 'Salary' THEN CONVERT(varchar(50), Salary)
        END 
ELSE 
    SELECT * FROM blat 
GO


-- Another way
Private Property SortField() As String
	Get
		Dim f_oSortField As Object = ViewState("SortField")
		Dim f_sSortField As String
		If f_oSortField Is Nothing Then
			f_sSortField = "CompanyName"
		Else
			f_sSortField = ViewState("SortField")
		End If
		Return f_sSortField
	End Get

	Set(ByVal Value As String)
		ViewState("SortField") = Value
	End Set
End Property

ORDER BY
CASE WHEN @sortField = 'CustomerID DESC' THEN CustomerID ELSE 0 END DESC, 
CASE WHEN @sortField = 'CustomerID' THEN CustomerID ELSE 0 END ASC,
CASE WHEN @sortField = 'CompanyName DESC' THEN CompanyName ELSE '' END DESC,
CASE WHEN @sortField = 'CompanyName' THEN CompanyName ELSE '' END ASC,
CASE WHEN @sortField = 'DateCreated DESC' THEN DateCreated ELSE '1/1/1900' END DESC, 
CASE WHEN @sortField = 'DateCreated' THEN DateCreated ELSE '1/1/1900' END ASC 


-- Order by non-alphabetically
ORDER BY (CASE [ItemStatus] 
	WHEN 'Available' THEN 1 
	WHEN 'Coming Soon' THEN 2
	WHEN 'Coming Soon/No Price Yet' THEN 3
	WHEN 'Approval Pending' THEN 4
	WHEN 'Approval Pending/No Price Yet' THEN 5
	ELSE 6 
	END )

Chain Replace

Change Password

-- change SA password

osql -U sa

enter password

sp_password @old = null, @new = 'complexpwd',  @loginame ='sa' 

go

Columns: Get All

Conditional And

-- Check if CardNumber has a value
SELECT @CardNumber = LTRIM(RTRIM(@CardNumber))
IF (@CardNumber = '' OR @CardNumber = 0)
BEGIN
	SELECT @CardNumber = NULL
END

-- Check if CustomerName has a value
SELECT @CustomerName = LTRIM(RTRIM(@CustomerName))
IF (@CustomerName = '')
BEGIN
	SELECT @CustomerName = NULL
END

SELECT...
WHERE...
AND C.CardSN = ISNULL(@CardNumber, C.CardSN)
AND C.Name = ISNULL(@CustomerName, C.Name)

Conditional AND With LIKE

AND C.Name LIKE ISNULL('%' + @CustomerName + '%', C.Name)

Constraint

ALTER TABLE NP_Enrollment 
    ADD CONSTRAINT IX_Email UNIQUE (NP_Email)

Constraint: Foreign Key

Constraint: Unique: Multiple Columns

Count

SELECT 
	COUNT(ProductId)
FROM
	tbl_OrderDetails
WHERE
	ProductID = 36

-- Count with alias
SELECT tblRegistrationWorkshop.Workshop_ID, Count(tblRegistrationWorkshop.Workshop_ID) AS CountOfWorkshop_ID
FROM tblRegistrationWorkshop
GROUP BY tblRegistrationWorkshop.Workshop_ID
ORDER BY tblRegistrationWorkshop.Workshop_ID

-- Count with alias, join
SELECT tblRegistrationWorkshop.Workshop_ID, tblEventWorkshop.Workshop_Name, 
Count(tblRegistrationWorkshop.Workshop_ID) AS CountOfWorkshop_ID
FROM tblEventWorkshop 
INNER JOIN tblRegistrationWorkshop ON tblEventWorkshop.Workshop_ID = tblRegistrationWorkshop.Workshop_ID
GROUP BY tblEventWorkshop.Workshop_ID, tblRegistrationWorkshop.Workshop_ID, tblEventWorkshop.Workshop_Name

Cursor

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  FUNCTION geteventnarrative (@EVENT_ID INTEGER)
RETURNS VARCHAR(8000)
AS

BEGIN
 DECLARE @NARR VARCHAR(48), @FULLNARR VARCHAR(8000)

 DECLARE narr_cursor CURSOR
 FOR SELECT en.narr
    FROM cm_events AS e INNER JOIN cm_eventnarr AS en
      ON e.ievent = en.ievent JOIN cm_caseevents AS ce
      ON e.ievent = ce.ievent
    WHERE ce.icaseevent = @EVENT_ID
    ORDER BY en.narrline
 FOR READ ONLY

 OPEN narr_cursor
 FETCH NEXT FROM narr_cursor INTO @NARR

 WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF (@@FETCH_STATUS <> -2)
      BEGIN
        SET @FULLNARR = COALESCE(@FULLNARR,'') + ' ' + COALESCE(@NARR,'')
        SET @FULLNARR = LTRIM(RTRIM(@FULLNARR))
      END
    FETCH NEXT FROM narr_cursor INTO @NARR
  END

 CLOSE narr_cursor
 DEALLOCATE narr_cursor

 RETURN @FULLNARR
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Database Commands

-- show all db info
EXEC sp_helpdb

-- create new db
CREATE DATABASE [Commerce]

-- rename db
EXEC sp_renamedb 'MyDB', 'MyDB2'

-- delete db
DROP DATABASE MyDB

Date: Between

AND Trxn_Date BETWEEN @StartDate AND @EndDate

Date: DateAdd

SELECT DATEADD(month, 2, OrderDate) 
FROM Orders

Date: DateDiff

UPDATE tbl_Schedule
SET ScheduleStatus = 0
WHERE DATEDIFF(dd, ScheduleExpires, GetDate()) > 1

Date: Format

ALTER FUNCTION FormatDate(@Date datetime)
	RETURNS varchar(10)
AS
BEGIN
	DECLARE @NewDate varchar(10)
	
	SET @NewDate = CONVERT(varchar, MONTH(@Date)) + '/' +
	CONVERT(varchar, DAY(@Date)) + '/' + CONVERT(varchar, YEAR(@Date))

	RETURN @NewDate
END

Delete

Desktop Shortcut

Detach

--(1)
use master
	go
	sp_detach_db 'myDB'
	go

--(2)
--Move the .mdf and ldf files to new location

--(3)
use master
	go
	sp_attach_db 'myDB','E:\Sql2k\Data\myDB.mdf','D:\Sql2k\Data\myDB.ldf'
	go

Do Not Display Seconds and Display AM PM

DTS

-- DTS RUN: SCHEDULED TASK
dtsrun /S 12bravosql /N "CUSTOMER IMPORT" /E

-- DTS RUN: COMMAND
dtsrun /S 12bravosql /N "CUSTOMER IMPORT" /UEGOV\Administrator /P mypass_ /E

Error: "Login failed for user...Not associated with a trusted..."

Error: SQL: "Saving changes is not permitted"

Error: SQL: "String or binary data would be truncated"

Exec

Exists

SELECT DISTINCT au_fname, au_lname, state
FROM authors
WHERE EXISTS
	(
	SELECT *
	FROM stores
	WHERE state = authors.state
	)

Having

HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions 
(like SUM), and without HAVING... it would be impossible to test for result conditions.

SELECT Company, SUM(Amount) 
FROM Sales
GROUP BY Company
HAVING SUM(Amount) > 10000

HelpFile

This command will give you all the info related to the database and log files.

use database_name
go
sp_helpfile
go

In

-- select
SELECT DISTINCT au_fname, au_lname, state
FROM authors
WHERE state IN
	(
	SELECT state 
	FROM stores
	)

-- update	
UPDATE company_user
SET user_role = (user_role + 12)
WHERE user_id IN 
	(
	SELECT user_id
	FROM company_user u
	INNER JOIN company_info i ON u.company_id = i.company_id
	WHERE i.ext_systemno <> 18
	AND i.company_type = 11
	)

Insert

INSERT INTO Products (ProductName, ProductPrice) VALUES ('hat', 10.00)

INSERT INTO Products (ProductName, ProductPrice) VALUES ( '" & product_name & "', " & product_price & " )

INSERT INTO LMD_Merchant_Access (User_ID, Company_ID)
SELECT User_ID, Company_ID
FROM LMD_Company_User

Insert From Another Table

SELECT
EDGE_AppId, EDGE_Code, Notes, Date, 'MeritCard Money', 1, Customer_ID, ID
FROM Customer_Notes
WHERE EDGE_AppId is not NULL

Insert or Update

SET NOCOUNT ON;

 IF EXISTS ( SELECT PlacementID
    FROM MyTable (NOLOCK)
    WHERE PlacementID = @PlacementID
     )     
    UPDATE MyTable
    SET MapKey = ISNULL(@MapKey,MapKey) ,
    iThemeID= ISNULL(@iThemeID, iThemeID)
    WHERE PlacementID = @PlacementID
 ELSE
  BEGIN
    INSERT INTO MyTable (MapKey, iThemeID, PlacementID)
    VALUES (@MapKey, @iThemeID, @PlacementID) 
        

    RETURN @@ERROR
  END

ISNULL

Join

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
INNER JOIN: keyword return rows when there is at least one match in both tables

-- INNER

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows 
in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

-- LEFT

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches 
in the right table (Orders).

-- RIGHT

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no 
matches in the left table (Persons).

-- FULL

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the 
right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there 
are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

Length

Linked Server

SELECT FROM SQL02.store.dbo.tbl_products

Loop

DECLARE @count INT
SET @count = 0
WHILE (@count < 3)
BEGIN
   select * from offices where officeid = 1000

   SET @count = (@count + 1)
END

Merge Data

-- Merge data from 2 different tables

UPDATE cj_main.dbo.tbl_rates
SET cj_main.dbo.tbl_rates.cost = tempdb.dbo.temp_import_profitability.cost
FROM tempdb.dbo.temp_import_profitability
WHERE cj_main.dbo.tbl_rates.tkinit = tempdb.dbo.temp_import_profitability.tkinit

Move Data From Another Table

Multiple Left

SELECT m.mmatter, country.udvalue AS 'Country', apptype.udvalue AS
'Application Type', busunit.udvalue AS 'Business Unit', projname.udvalue AS 'Project Name',
projnum.udvalue AS 'Project Number', costcent.udvalue AS 'Cost Center',
ordnum.udvalue AS 'Int. Order Number', strategic.udvalue AS 'Strategic
Revenue Group'
FROM matter AS m
LEFT OUTER JOIN udf AS country ON m.mmatter = country.udjoin
AND country.udtype = 'MT'
AND country.udfindex = 8
LEFT OUTER JOIN udf AS apptype ON m.mmatter = apptype.udjoin
AND apptype.udtype = 'MT'
AND apptype.udfindex = 226
LEFT OUTER JOIN udf AS busunit ON m.mmatter = busunit.udjoin
AND busunit.udtype = 'MT'
AND busunit.udfindex = 227
LEFT OUTER JOIN udf AS projname ON m.mmatter = projname.udjoin
AND projname.udtype = 'MT'
AND projname.udfindex = 228
LEFT OUTER JOIN udf AS projnum ON m.mmatter = projnum.udjoin
AND projnum.udtype = 'MT'
AND projnum.udfindex = 229
LEFT OUTER JOIN udf AS costcent ON m.mmatter = costcent.udjoin
AND costcent.udtype = 'MT'
AND costcent.udfindex = 230
LEFT OUTER JOIN udf AS ordnum ON m.mmatter = ordnum.udjoin
AND ordnum.udtype = 'MT'
AND ordnum.udfindex = 231
LEFT OUTER JOIN udf AS strategic ON m.mmatter = strategic.udjoin
AND strategic.udtype = 'MT'
AND strategic.udfindex = 232
WHERE m.mjnum = '27475BRAVO'

Nolock

Using NOLOCK asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.

SELECT *
FROM tbl_member WITH (NOLOCK)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Optional Parameter

Order By Case: Sort

ORDER BY  
	CASE WHEN @sortField = 'CardSN' THEN C.CardSN ELSE 0 END DESC, 
	CASE WHEN @sortField = 'LastUsed' THEN C.LastUsed ELSE '1/1/1900' END DESC,
	CASE WHEN @sortField = 'Name' THEN C.Name ELSE '' END ASC
END

Query

PARAMETERS ID Long

SELECT tblPerson.Person_First_Name, tblPerson.Person_Last_Name
FROM tblPerson 
INNER JOIN tblRegistration ON tblPerson.Person_ID = tblRegistration.Person_ID
WHERE (((tblRegistration.Event_ID) = [ID]))
ORDER BY tblPerson.Person_Last_Name

Replace Bulk

Restore

-- Restore from an existing DB to a new DB

(1) Create a new DB in Enterprise Manager.
(2) Right Click on New DB and select "All Tasks > Restore Database".
(3) Keep the "Database" radio checked and select the DB that you want to restore from in the 
    "Show backups of database:" dropdown.
(4) Verify the checked backup files.
(5) Switch to the "Options" tab and check "Force restore over existing database".
(6) Verify the "Restore database files as:" section.
(7) Click "Ok".

RowId

Select

SELECT ProductName 
FROM Products 
WHERE ProductPrice > 10.00 
AND ProductPrice < 30.00

SELECT ProductName 
FROM Products 
WHERE Category = 'video games' 
ORDER BY ProductName

SELECT DISTINCT Category 
FROM Products 
ORDER BY Category

SELECT TOP 5 * 
FROM Products

Select Case

Select Into

INSERT INTO STORE_INVOICE (cust_id, bill_period, line_sequence, inv_item, inv_amt, username)
SELECT bill_cust_id, p_CURRENT_BILL_PERIOD, line_sequence, inv_item, inv_amt, p_in_USERNAME
FROM STORE_COMMENT 
WHERE bill_cust_id = p_BILL_CUST_ID 
AND bill_period = p_BILL_PERIOD

SELECT *
INTO Persons_Backup
FROM Persons 

-- We can also use the IN clause to copy the table into another database:

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons 

-- We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

-- Using a join:

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id = Orders.P_Id

ShrinkFile

--(1) Backup Log

-- (a)
BACKUP LOG TRACKIT70_DATA2 TO DISK='C:\Back\TRACKIT70_DATA2_log.bak'

-- (b) Backup Log with Truncate
BACKUP LOG TRACKIT70_DATA2 WITH TRUNCATE ONLY

--(2) Shrink

-- (a)
DBCC SHRINKFILE (TRACKIT65_DATA_log, 500) WITH NO_INFOMSGS

-- (b)
DBCC SHRINKFILE (2, 2048) WITH NO_INFOMSGS

SQL CharIndex

SQL Native Client

(1) Open Notepad and save as .udl file and close the file

(2) Double click on the file and open 'Provider' tab

(3) Look for 'SQL Native Client' in the list of OLE DB Provider(s)

(4) If it is not there, go to ...

	http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

	(Microsoft SQL Server Native Client)

(5) Install it

(6) Use the following connection string in your .Net app to access SQL 2005...

	<add key="ConnectionString"
	value="Provider=SQLNCLI.1;Password=111111;Persist Security
	Info=True;User ID=sa;Initial Catalog=IFIX;Data Source=111.111.111.111"/>

Stored Procedure

ALTER Procedure sp_ProductsByCategory
(
    @CategoryID int
)
AS

SELECT 
    tbl_Products.ProductID,
    tbl_Products.ProductName,
    tbl_Schedule.ScheduleStartDate, 
    tbl_Schedule.ScheduleEndDate,
    tbl_Schedule.ScheduleStatus,
    tbl_Products.UnitCost,
    tbl_Schedule.ScheduleID
FROM tbl_Products 
INNER JOIN tbl_Schedule ON tbl_Products.ProductID = tbl_Schedule.ProductID
WHERE CategoryID = @CategoryID
ORDER BY ProductName, ProductNumber

Stored Procedure: Return Parameter

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER Procedure usp_UserDelete
/*
Name: 		usp_UserDelete

Author:		Charles Janco

Date: 		04/18/2005

Description: 	Delete user.
_______________________________________________________________________
	04/18/2005	CGJ	Initial Release
*/
(
	@User_Name varchar(20)
)
AS

DECLARE	@User_ID int

SELECT	@User_ID = U.User_ID
FROM	tbl_Company_User U
WHERE	U.User_Name = @User_Name
IF (@@ROWCOUNT != 1)
	BEGIN
	RETURN 1
	END
DELETE tbl_User_Access WHERE User_ID = @User_ID
DELETE tbl_Company_User WHERE User_ID = @User_ID
RETURN 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Sub Query

Sub Select

SubString

SELECT tkinit, mmatter, tworkdt,
      SUM(CASE SUBSTRING(period,1,2)
            WHEN '01' THEN janhrs
            WHEN '02' THEN febhrs
            WHEN '03' THEN marhrs
            WHEN '04' THEN aprhrs
            WHEN '05' THEN mayhrs
            WHEN '06' THEN junhrs
            WHEN '07' THEN julhrs
            WHEN '08' THEN aughrs
            WHEN '09' THEN sephrs
            WHEN '10' THEN octhrs
            WHEN '11' THEN novhrs
            WHEN '12' THEN dechrs
          END) AS tworkhrs
FROM Reports

SysObjects Table

SQL Server uses a table to store the object information for each database. This table, sysobjects, 
can be queried just like any other table. If you wanted to display the tables in your database on 
a web page. All of the tables in your database are stored in the sysobjects table, so all you need 
to do is properly query the sysobjects table.

IF EXISTS 
	(
	SELECT * FROM dbo.sysobjects 
	WHERE Id = OBJECT_Id(N'[dbo].[spSearch]') AND OBJECTPROPERTY(Id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spSearch]
GO

CREATE PROCEDURE [dbo].[procGetUserForSearch]
(
	@Id VARCHAR(1500)
)
WITH ENCRYPTION 
AS
BEGIN
	SET NOCOUNT ON
	...
	FOR XML EXPLICIT, BINARY BASE64
END
GO

SysObjects: Default

IF EXISTS 
	(
	SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[dfltOne]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
	DROP DEFAULT [dbo].[dfltOne]
GO

CREATE DEFAULT [dbo].[dfltOne] AS 1
GO

Table Commands

-- create table
CREATE TABLE Customers
(
	CustID char(8) NOT NULL,
	CustName varchar (30) NOT NULL,
	Email varchar (50) NOT NULL
)

-- create table
IF EXISTS 
	(
	SELECT * 
	FROM dbo.sysobjects 
	WHERE Id = OBJECT_Id(N'[dbo].[tArticles]') 
	AND OBJECTPROPERTY(Id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[tArticles]
GO

CREATE TABLE [dbo].[tArticles]
(
	[ArticleId] INT IDENTITY (1, 1) NOT NULL,
	[ArticleTypeId] INT NOT NULL,
	[Name] VARCHAR(50) NOT NULL,
	[Pattern] VARCHAR(200) NULL,
	[Disabled] BIT NOT NULL,
	[CDate] DATETIME NOT NULL,
	[UDate] DATETIME NOT NULL,
	[PostToWeb] BIT NOT NULL
	CONSTRAINT [pk_tArticles] PRIMARY KEY NONCLUSTERED
	(
		[ArticleId]
	)
)
GO

EXEC SP_BINDEFAULT N'[dbo].[dfltDisabled]', N'[tArticles].[Disabled]'
EXEC SP_BINDEFAULT N'[dbo].[dfltSysDate]', N'[tArticles].[CDate]'
EXEC SP_BINDEFAULT N'[dbo].[dfltSysDate]', N'[tArticles].[UDate]'
EXEC SP_BINDEFAULT N'[dbo].[dfltOne]', N'[tArticles].[PostToWeb]'
GO

Transfer Logins

Transfer logins between 2 SQL 2000 servers.

How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that 
are running SQL Server 2000 To transfer logins and passwords from a SQL Server 7.0 server to an
instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS 
Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps: 

1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in 
SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.

2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information 
about the Source, Destination and Logins tabs as appropriate.

Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS 
components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an 
instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under 
a Domain Account to complete the task.

Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using 
the original SID and user databases are also transferred to a new server, the database users will be orphaned 
from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete
resolution to transfer logins and passwords between different versions of SQL Server" section.

Truncate

Union

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The UNION operator is used to combine the result-set of two or more SELECT statements. The UNION command 
selects only distinct values. To list all, use UNION ALL.

Unique Constraint

Unload

Use this statement to export data from a database table into an external ASCII-format file.

UNLOAD TO 'clrefer' DELIMITER '!'
SELECT clnum, clrefer
FROM client
WHERE clrefer IS NOT NULL

Update

UPDATE Products SET ProductName = 'shirt' WHERE ProductPrice = 20.00

Update one Table with Value from Another Table

Update: Where Exists

UPDATE Customer_Notes 
SET EDGE_AppId = (SELECT Merchants.AppId
FROM Merchants
WHERE Merchants.MCMoneyId = Customer_Notes.Customer_Id)
WHERE EXISTS
  ( SELECT Merchants.AppId
    FROM Merchants
    WHERE Merchants.MCMoneyId = Customer_Notes.Customer_ID);

Users

-- show all current users
sp_who2

-- create SQL login
EXEC sp_addlogin 'webuser', 'password'

-- change SQL login password
EXEC sp_password 'password', 'newpassword'

-- grant SQL login to Windows account
EXEC sp_grantlogin 'WEBSQL01\cjanco'

-- remove SQL login from Windows account
EXEC sp_revokelogin 'WEBSQL01\cjanco'

-- grant access to a DB for SQL account or Windows account
USE store
EXEC sp_grantdbaccess 'webuser'

Users: Show All

Variable

DECLARE	@User_ID int
SET @User_ID = 1927

SELECT Merchant_ID
FROM MerchantAccess
WHERE User_ID = @User_ID