T-SQL Basic Command Examples

Some of these snippets are useful when scripting schema changes, as opposed to using the GUI tools. For example, we have several instances of the “internal” DB – some production DBs for the various [business location]s, and several test DBs – and when we make a schema change to one, we generally want to make the same change to all. Rather than perform the same GUI actions umpteen times, it’s much easier to create a change script, and run the script against [List-of-databases|each DB].

Add a new domain user login

CREATE LOGIN MyDomain\joeUser
FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks2012;

1 2	CREATE LOGIN MyDomain\joeUser FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks2012;

Add a new SQL Server-authenticated login

CREATE USER joeUser WITH PASSWORD = 'myPassw0rd!',
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON,
DEFAULT_DATABASE = AdventureWorks2012;

CREATE USER joeUser WITH PASSWORD = 'myPassw0rd!',

CHECK_POLICY = ON,

CHECK_EXPIRATION = ON,

DEFAULT_DATABASE = AdventureWorks2012;

Create table

CREATE TABLE foo
(
    fooID INT NOT NULL,
    barID INT NULL,
    baz NVARCHAR(100) CONSTRAINT DF_foo_baz DEFAULT '',
    CONSTRAINT PK_foo PRIMARY KEY (fooID),
    CONSTRAINT FK_foo_bar FOREIGN KEY (barID) REFERENCES bar(barID)
)

CREATE NONCLUSTERED INDEX IX_foo_barID ON foo(barID)

-- Add human-readable (comment-like) column description
EXEC sys.sp_addextendedproperty
	@name=N'MS_Description',
	@value=N'[description]',
	@level0type=N'SCHEMA',
	@level0name=N'dbo',
	@level1type=N'TABLE',
	@level1name=N'[table name]',
	@level2type=N'COLUMN',
	@level2name=N'[column name]'
GO

CREATE TABLE foo

(

fooID INT NOT NULL,

barID INT NULL,

baz NVARCHAR(100) CONSTRAINT DF_foo_baz DEFAULT '',

CONSTRAINT PK_foo PRIMARY KEY (fooID),

CONSTRAINT FK_foo_bar FOREIGN KEY (barID) REFERENCES bar(barID)

)

CREATE NONCLUSTERED INDEX IX_foo_barID ON foo(barID)

-- Add human-readable (comment-like) column description

EXEC sys.sp_addextendedproperty

@name=N'MS_Description',

@value=N'[description]',

@level0type=N'SCHEMA',

@level0name=N'dbo',

@level1type=N'TABLE',

@level1name=N'[table name]',

@level2type=N'COLUMN',

@level2name=N'[column name]'

Add column & default
Remember to give [HERE-database-naming-standards|names] to constraints. Auto-generated constraint names like DF__fooNa__colum__63AA1F03 can be very difficult to work with across multiple DB instances.

ALTER TABLE foo ADD
    bar REAL NOT NULL CONSTRAINT DF_foo_bar DEFAULT 0,
    baz VARCHAR(20) NOT NULL CONSTRAINT DF_foo_baz DEFAULT ''

ALTER TABLE foo ADD

bar REAL NOT NULL CONSTRAINT DF_foo_bar DEFAULT 0,

baz VARCHAR(20) NOT NULL CONSTRAINT DF_foo_baz DEFAULT ''

Add column with foreign key

ALTER TABLE foo
    ADD bar INT NULL,
CONSTRAINT FK_foo_baz FOREIGN KEY (bar) REFERENCES baz(bar)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION

ALTER TABLE foo

ADD bar INT NULL,

CONSTRAINT FK_foo_baz FOREIGN KEY (bar) REFERENCES baz(bar)

ON UPDATE NO ACTION

ON DELETE NO ACTION

Add default constraint to existing column

ALTER TABLE foo
    ADD CONSTRAINT DF_foo_bar DEFAULT 0 FOR bar

1 2	ALTER TABLE foo ADD CONSTRAINT DF_foo_bar DEFAULT 0 FOR bar

Remove column & default

ALTER TABLE foo
    DROP CONSTRAINT DF_foo_bar, COLUMN bar

1 2	ALTER TABLE foo DROP CONSTRAINT DF_foo_bar, COLUMN bar

Rename table or constraint

EXEC sp_rename 'foo', 'bar'

1	EXEC sp_rename 'foo', 'bar'

Rename column

EXEC sp_rename
    @objname = 'tableName.foo',
    @newname = 'bar',
    @objtype = 'COLUMN'

EXEC sp_rename

@objname = 'tableName.foo',

@newname = 'bar',

@objtype = 'COLUMN'

Get info about table Foo

sp_help Foo

1	sp_help Foo

Table variables
Unlike temp tables, these only live in the scope of the current query, and are automatically cleaned up (no need to DROP, like temp tables).

DECLARE @tempData TABLE(id int)
INSERT INTO @tempData(id)
    VALUES(1),(2),(55),(123),(9)

SELECT *
FROM foo
WHERE fooID IN (SELECT id FROM @tempData)

SELECT *
FROM bar
WHERE barID IN (SELECT id FROM @tempData)

DECLARE @tempData TABLE(id int)

INSERT INTO @tempData(id)

VALUES(1),(2),(55),(123),(9)

SELECT *

FROM foo

WHERE fooID IN (SELECT id FROM @tempData)

SELECT *

FROM bar

WHERE barID IN (SELECT id FROM @tempData)

Temp table

CREATE TABLE #temp
(
    foo INT,
    bar VARCHAR(20)
)

-- Concise version of INSERT syntax
INSERT INTO #temp
    VALUES (127, 'abc'), (284, 'def'), (843, 'zyx')

SELECT * FROM #temp

DROP TABLE #temp

CREATE TABLE #temp

(

foo INT,

bar VARCHAR(20)

)

-- Concise version of INSERT syntax

INSERT INTO #temp

VALUES (127, 'abc'), (284, 'def'), (843, 'zyx')

SELECT * FROM #temp

DROP TABLE #temp

Bulk load data from CSV file
Using a temp table, though it is possible to bulk-load data directly into a “permanent” table.

CREATE TABLE #temp
(
    foo INT,
    bar VARCHAR(20)
)

BULK INSERT #temp
FROM 'path\to\whatever.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

SELECT * FROM #temp

DROP TABLE #temp

CREATE TABLE #temp

(

foo INT,

bar VARCHAR(20)

)

BULK INSERT #temp

FROM 'path\to\whatever.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

SELECT * FROM #temp

DROP TABLE #temp

UPDATE multiple columns from one SELECT

UPDATE table1
SET foo = a.foo,
    bar = a.bar,
    baz = a.baz
FROM (SELECT b.foo, b.bar, b.baz
      FROM table2 b) AS a
WHERE ...

UPDATE table1

SET foo = a.foo,

bar = a.bar,

baz = a.baz

FROM (SELECT b.foo, b.bar, b.baz

FROM table2 b) AS a

WHERE ...

UPDATE table1
SET t1.foo = t2.foo
FROM table1 t1
    INNER JOIN table2 t2 ON t2.ID = t1.ID

UPDATE table1

SET t1.foo = t2.foo

FROM table1 t1

INNER JOIN table2 t2 ON t2.ID = t1.ID

INSERT multiple hard-coded records into table
Other SQL implementations allow for less verbose multi-record insert.

INSERT INTO TABLE (int1, int2, string1)
SELECT 1, 2, 'foo'
UNION ALL
SELECT 3, 4, 'bar'
UNION ALL
SELECT 5, 6, 'baz'

INSERT INTO TABLE (int1, int2, string1)

SELECT 1, 2, 'foo'

UNION ALL

SELECT 3, 4, 'bar'

UNION ALL

SELECT 5, 6, 'baz'

Views – 1856

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, .Net etc…

T-SQL Basic Command Examples

Leave a Reply Cancel reply