SQL Server Changing Table Structure

 in SQL Server  tagged table / add / column / alter / structure by

The table structure refers to the columns in the table. We can use T-SQL to add a new column to the end of the table, for example. We can also delete columns from a table. If we drop a column we will lose all of the data that might have been in the column.

Alter

alter table tblNames -- Table tblNames already exists
   add LastName varchar(50) NULL;  -- you cannot use the word "column" here

alter table tblNames
   drop column LastName;  -- you need the word "column" here

alter table tblNames            -- change the data type to varchar(60)
   alter column LastName varchar(60) NULL;
-- this command retains the data in it as far as it 
-- is able to do. Since we have increased the size
-- we will not lose any data.

Drop

While we are on the topic we can also drop (delete) a table and all of the data in it. 

drop table tblNames

If you try to alter a table by reducing the size of a column like in the code below, you may get the following message. 

alter table tblName
alter column Department varchar(1)

Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.

You can use the SQL LEN function to get the length of a string in characters. 

select len('twenty-one chars long')

Insert

We can insert data into a table when the data is in a different order by specifying the columns. Below is the create table code and it is followed by the code to insert a row. This works fine. 

CREATE TABLE [dbo].[tblPeople2](
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Amount] [int] NULL
) 
insert into tblPeople
(Amount,FirstName,LastName)
values(23,'John','Smith')

If we try to insert this unordered data (23,’John’,’Smith’) and we did not specify the correct column names we will get the following error. 

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Smith' to data type int.

Notice that the following query does in fact work because the numbers are successfully converted into varchar(). 

insert into tblPeople
values(23,24,25)