T-SQL Example code: Table Management

T-SQL table management tips, tricks and code snippets

Create a Table

CREATE TABLE test (FirstName VARCHAR (20), LastName VARCHAR (20), DateOfBirth DATETIME)

 

Add a column to a table

USE DatabaseName;
ALTER TABLE TableName ADD [Version] varchar(50)

Copy Table Structure and (some or all) Data

Create a table using existing table data and structure ensuring the data doesn’t contain duplicates and then add a couple of extra columns

USE DatabaseName;

 SELECT custid, forename, surname

  INTO AAtest1

  FROM Profile

  GROUP BY custid, forename, surname

  ORDER BY custid;

GO

ALTER TABLE AAtest1

  ADD fornameold VARCHAR(35);

ALTER TABLE AAtest1

  ADD surnameold varcahr(35);

go

Copy Table Strucutre to New Table

SELECT * INTO NewTable FROM OldTable WHERE 1=0;

or being more specific about the columns:

SELECT custid, firstname, surname INTO NewTable FROM OldTable WHERE 1=0;

Change a Column Type or Parameters

ALTER TABLE lending ALTER COLUMN bookID int

Add a constraint to a column

ALTER table logins

ADD CONSTRAINT admin_default 

DEFAULT '0' for admin

Drop a Table from a Database

USE databaseName;

DROP TABLE tableName;

Remove all data from a table

truncate table tableName

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

Leave a Reply