• Create
  • Alter
  • Drop
  • Truncate.

Create Command in SQL Server

Creating a table from another table  :-

             Syntax :-  

                Select <collist> into <New tabname>

                From <old tabname>

                 [where <cond>]

Example:–     Select * into <new emp10 from emp.

{all stracture  &data is copyed from emp table to new table.}

Coping particular columns &particular records :

                  Select empno ,ename,sal

                  Into emp 11

Coping only structure but not data :

                   Select * into emp12

                   From emp where     1=2  {Here we can give  any false condition}

Coping only the data but not structure  :-

  • It is not possible.

(2) Alter Command in SQL Server :-

  • Alter command is used to modify structure of a table.
  • Use alter command to do the following things  :

       1. Adding new column

       2. Droping column

       3. Modifying column ->[incr | DECR field size] -> [changing data type]

(i ) Adding new columns  :-   

Syntax : Alter table <tabname>

                  Add colname data type [size]

Example:   Alter table emp11

                       Add job varchar(20)

(ii)Drpping column  :  

Syntax  :         Alter table <tablename>

                           Drop column name.

Example:–  Alter table emp11

                          Drop column job.

{Note  :  A table is there atleast one column must there}

(iii)Modifying column   :    

Synatx : alter table <tabname>

                  Alter column colname data type(size)

Example:  Alter table emp

                      Alter column ename varchar(20)

Note: Decrement is possible up to max. length


Example: sp_help  emp.

  • This command show structure of table.
  • Sp stands for stored procedure.

Note: — To change a data type of the column,column must be empty.

Drop Command in SQL Server   :-

  • Drop command is used to drop the table from the database.

Syntax   :  drop table<tabname>

Example: drop table EMP.

TRUNCATE Command in SQL Server :-

  • To delete all records from table (or) makes table empty.

Syntax : Truncate table <tabname>

Example:   truncate table emp11.