Tuesday, 10 September 2013

Tools & Tech - Add / Delete a Table from a database

Now that we have a SQL database up and running, we need to add Tables. As with adding the Database, this can either be done via the user interface or by running a set of SQL queries. As before, we'll do both.

Adding a Table to a Database (via UI)
If you're not already; Log-into your SQL server via SSMS, open the Object Explorer window (‘View > Object Explorer’ or hit F8), then expand 'Databases' so you can see your test database.
  •         Expand your test database, e.g. ‘TestDB001’ then
  •     Right-click on Tables and select ‘New Table…’ to open the table designer window

Enter the column names you want to use and select the appropriate data type. There is a full list of data types given here http://technet.microsoft.com/en-us/library/ms187752.aspx.

We're going to set up two tables, one called EmpGrade to store data about the Employee Grade, the other called SalesTeam which has the list of Sales Team members.

Add the following columns into the Table Designer, selecting the correct data type and select if the column can have null data or if it must be populated with data. For all the following I've set them to NOT NULL, except SalaryMax.

  • GradeID (int)
  • SalaryMax (int)
  • EmpID (int)
  • FirstName (varchar(50)
  • LastName (varchar(50))
  • Location (varchar(50))
  • Grade (int)
Make GradeID and EmpID the Primary Key of each table by right-clicking on the tab to the left and selecting 'Set Primary Key'. We'll come back to setting a Foreign Key in a later post.

For each, click 'File > Save Table1...' and save your tables.

Deleting a Table in a Database (via UI)
Deleting a table via the UI is straight forward. Simply select your table and right-click. In the context menu that appears, click on Delete.

Delete your temporary test tables and we'll re-create them using SQL statements. If you want to keep them you can just make some additional tables.

Adding a Table to a Database (via SQL)
Doing all this via the UI is fine, but this isn't a SQL Server training tutorial, it's about SQL statements hands-on, so let's look at that. 
To add a new table using a SQL statement is as straight forward as the UI. We're also able to set parameters such as the Primary Key directly.

On the menu bar, select 'New Query' and enter the following:

create table EmpGrade
GradeID int NOT NULL Primary Key,
SalaryMax int NOT NULL

Hit F5 to execute the statement.

We've now created a new table using SQL and set GradeID as the Primary Key. If you right-click on 'Tables' in the Object Explorer window then select 'Refresh', you will see your new table is there.

To add the second table from our example, run the following query:

create table SalesTeam
EmpID int NOT NULL Primary Key,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Location varchar(50) NOT NULL,
Grade int NOT NULL,

Hit F5 to execute the statement and refresh your tables list.

Deleting a Table in a Database (via SQL)
Deleting a table requires just a short SQL statement.

drop table SalesTeam

Then hit F5 to execute the statement and you'll see your table is deleted. Remember you might have to 'Refresh' the tables list to see the change.

Have a watch of the video on YouTube that covers all these points - linked to above



Unknown said...
This comment has been removed by a blog administrator.
Unknown said...
This comment has been removed by a blog administrator.