Transact SQL 101 - Lesson 06_01_SQL.txt

USE adventureworkslt
GO

SELECT * FROM saleslt.address
WHERE modifieddate > '5/17/07'

--Example 1
INSERT INTO SalesLT.Address
		(AddressLine1, City, StateProvince, CountryRegion, PostalCode)
VALUES ('75 W. 66th Street','New York','NY','United States','10023')

--Example 2
INSERT INTO saleslt.address
VALUES ('11 Waverly Place', 'Apt 11C','New York', 'NY', 'United States', '10011', NEWID(), GETDATE())

INSERT INTO saleslt.address
VALUES ('1600 Pennsylvania Avenue', NULL, 'Washington', 'DC', 'United States', '20500', DEFAULT, DEFAULT)

--SELECT * FROM ##addresstemp
--Example 3
INSERT INTO saleslt.address 
		(AddressLine1, AddressLine2, City, StateProvince, CountryRegion, PostalCode)
SELECT address1, address2, city, state, country, zip
FROM ##addresstemp
WHERE state = 'TX'

--Data Prep (INSERT)
CREATE TABLE ##AddressTemp
 (
 address1 nvarchar(60),
 address2 nvarchar(60),
 city nvarchar(30),
 state nvarchar(50),
 country nvarchar(50) DEFAULT 'United States',
 zip nvarchar(15)
 )

INSERT INTO ##AddressTemp VALUES ('15 Main Street', NULL, 'Dallas', 'TX', DEFAULT, '75204')
INSERT INTO ##AddressTemp VALUES ('23 McKinney Ave','Apt. 221','Dallas','TX', DEFAULT, '75206')
INSERT INTO ##AddressTemp VALUES ('493 Lahoma Ave',NULL,'Norman','OK', DEFAULT, '73069')
INSERT INTO ##AddressTemp VALUES ('55 Market Street',NULL,'Kansas City','KS', DEFAULT, '72354')
INSERT INTO ##AddressTemp VALUES ('950 3rd Avenue','Suite 900','New York','NY', DEFAULT, '10022')
INSERT INTO ##AddressTemp VALUES ('11 Wall Street',NULL,'New York','NY', DEFAULT, '10005')