T-SQL Example code: Select, Insert, Update, Delete

Various examples and snippets of Select, Insert, Update, Delete T-SQL

Select Examples

SELECT COUNT(forename) FROM Table1 WHERE forename like 'simon';
SELECT custid, forename, surname FROM table1 

	WHERE (forename IS not null) 

	ORDER BY forename, surname
select * from test_Results 

	where Test_Name like 'ServerService%'

Latest date only:

select top 1 *from BO_alive order by ba_run_time desc

How many states do we have customers in?

SELECT COUNT(DISTINCT customer_state) AS total FROM customers
select top 1 *from BO_alive order by ba_run_time desc

Inner Join Example

The database has two tables (gender and people), gender contains 1 a PK (GenderID) and 1 column (Gender). People table contains a few columns (p_name, p_age, p_town, gender). genderID is linked to genderID.gender table.

Here are 2 example joins that produce the same result.

SELECT p_name, p_Age, p_town, gender

	FROM people INNER join gender 

	ON (people.genderID = gender.genderID)


 SELECT p_name, p_Age, p_town, gender 

	FROM people, gender

	WHERE people.genderID = gender.genderID

Adding a third table of country, with countryID and country columns, we can use the following to select all records:

SELECT p_name, p_Age, p_town, gender, country 

	FROM people, gender, country

	WHERE country.countryID = people.countryID

	and gender.genderID = people.genderID;

Select part of a record only

SELECT SSLogin,Left(UserLogon, PatIndex('%@%', UserLogon + '1') - 1) as SSLogin, SSDisplayname, Firstname, Lastname 


	where (BS.DisplayName = ss.ssdisplayName)

or similarly for a fixed amount chopped off the right:

SELECT SSLogin,Left(UserLogon,(len (UserLogon)-9)) as SSLogin, SSDisplayname,Firstname,Lastname 


	where (BS.DisplayName = ss.ssdisplayName)

Update Examples

Update is used to update EXISTING data, the syntax is:



UPDATE customers SET salesperson = 'Mike' WHERE STATE = 'NH'

Update one column based on a column in another table

UPDATE nonVSphereVMs 

set vmIP = dns.ip from dns

where nonVSPhereVMS.vmDNS like dns.DNSname+'%'
UPDATE customers SET salesperson = 'Mike' WHERE STATE LIKE 'N%'
update Components SET CheckedOutLocation = 'D:\dsa-temp-se\' 

WHERE ItemName = 'DSA_2.pds'

Delete Examples

DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00;

Clear down or elete all rows from a table

DELETE FROM Sales.SalesPersonQuotaHistory;

Delete rows based on a subquery

USE AdventureWorks2008R2;


DELETE FROM Sales.SalesPersonQuotaHistory 

WHERE BusinessEntityID IN 

    (SELECT BusinessEntityID 

     FROM Sales.SalesPerson 

     WHERE SalesYTD > 2500000.00);


Insert Examples

Insert is used to squirt in NEW data

INSERT INTO customers(customer_id, customer_name)

    VALUES('12345', 'GIS Experts')

Copying Data

Copy data from one table to another

SELECT * into countrybackup

	FROM country

Copy table from one database table to another database

SELECT * into Test2.dbo.country

	FROM test1.dbo.country

Copy data from one table in to a pre existing table in another database

INSERT INTO test2.dbo.country

SELECT * FROM test1.dbo.country

Posted on July 12, 2012 at 23:48 by simon · Permalink
In: SQL · Tagged with: , , ,

Leave a Reply