Creating, Altering and Inserting data into tables

Creating Table:

1. You can find all the table information in "information_schema.tables" system table.
2. It is best practice to find the existence of the table before creating so that even if we run the create script 2nd time, it won't give errors.

Syntax: 

CREATE TABLE [TABLENAME]([columnname] dataType, [columnName] dataType,...)
GO

    

Example:

 CREATE TABLE Test (Id int, Name Varchar(50));

 GO

Example:

Alter Table:

      Alter statement is used to Add or drop the columns and change the column datatypes and also adding the constraints.

Adding column:

Syntax:

ALTER TABLE [TABLENAME] ADD [COLUMNNAME] DATATYPE

 GO

      

Example:

ALTER TABLE Test ADD LastName Varchar(20)

 GO

      

- It will add LastName column to Test table.

Renaming the column:

Syntax:

SP_Rename 'TABLENAME.ColumnName','NewCOLUMNNAME', 'COLUMN'

 GO

      

Example:

SP_Rename 'Test .Name','FirstName', 'COLUMN'

 GO

      

- It will rename Name column FirstName.

Dropping column:

Syntax:

ALTER TABLE [TABLENAME] DROP COLUMN  [COLUMNNAME] 

 GO              

  

Example:

 ALTER TABLE Test DROP COLUMN LastName

 GO              

        

- It'll Drop LastName column form Test table.

Changing Column Datatype:

Syntax:

 ALTER TABLE [TABLENAME] ALTER COLUMN [COLUMNNAME] DATATYPE

 GO              

        

Example: 

 ALTER TABLE Test ALTER COLUMN LastName Varchar(100)

 GO              

   

- It'll change the datatype from varchar(50) to varchar(100)

Inserting Data:

Syntax: 

 INSERT INTO TABLE [TABLENAME] VALUES(1ColumnValue,2ColumnValue,,...)

 GO               

Example:

    INSERT INTO Test Values(1,'Rajesh')

    GO

    INSERT INTO Test Values(2,'Raju')

    GO        

If you want to know adding constraints to table read Adding Constraints.

Please feel free to comment if you need any other help.