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

Add a new SQL Server-authenticated login

Create table

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.

Add column with foreign key

Add default constraint to existing column

Remove column & default

Rename table or constraint

Rename column

Get info about table 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).

Temp table

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

UPDATE multiple columns from one SELECT

OR

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

Views – 463

Leave a Reply