DDL COMMANDS :-
- 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
Sp_help<tabneame>
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.