Create a Table from Another table :-

Download Script  from Here
Abstract:

  In this artical we will learn how to create table from another table without creating another table.


Agenda:

1)Create a Table from Another table .
2)Inserting Row from one table  to Another Table.
3)Modify the Data in  Table.
4)Update rows of table in GUI.
5)Computed Columns.


Lets Learn in details,

Create a Table from Another table :-

           To create copy of existing table use the following syntax

select */<Columnn List> into  <New Table Name> From <Old Table Name>

eg.

folowing  table create the copy of the table student form with the name stud

select * into tbl_copy from student

insert table with only specefic columns  or Conditions.

select  *  into tbl_Copy1 from tbl_Emp where FName='abhijit'

while creating a table from another table if you dont

to copy the data then specify a condition on the where clause which will be false

select * into student2 from Student where 1=0

Note

      While creating  a table from another table constraints and  indexes available on existing table will not be copy to new table

------------------------------------------------------------

Inserting Row from one table  to Another Table:-

        

You can insert rows from one tables to another provided those tables have columns with same datatype and for this use the following syntax of insert statement.

insert into <Table Name> <Select Statment>

eg:

insert tbl_Copy(StudentId,FirstName ,LastName )select StudentId,FirstName,LastName from Student

Modify the Data in  Table:-

        

To modify the existing data in that table ,you  have to use update command  that has the following  syntax.

Update <tableName> set <ColName>=<Value>,<ColName>=<Value>.......[Where <Condition>]

Eg:

The following examples update th first name of student id 1 to the ‘suhas’

update Student  set FirstName='Suhas' where StudentId =1

Update rows of table in GUI:

  Open the table by right click on the table in object explorer and choosing “Edit 200 Rows” this will open the table and display all exisiting row in the table.

 Whatever value you want modify ,click on that value modify the value and move to the previous or next to complete the update.

Computed Columns:-

  Computed columns are columns for which rows is automatically computed during insert.

  To create a columns as computed columns ,while creating that table after the columnname use the keyword as   followed by the formula based on which the value has to be computed for that columns.

   For the computed columns ,you cannot specify the datatype while insert   the rows into the table containg computed columns ,you must exclude the computed columns and you can not provide values for computed columns manual.

Eg:

   Following example create table with the name marks crating  the columns total,avg,and grade as computed columns.

create table mark(sid int ,c int,cpp int,sql int,total as c+cpp+sql,aveg as (c+cpp+sql)/3.0,

grade as case

when (c+cpp+sql)/3.0 >=70 then 'First Class'

when (c+cpp+sql)/3.0 >=55 then 'SecondClass'

when (c+cpp+sql)/3.0 >=35 then 'Thired Class'

else

'fail'

End)

Insert values using the following syntax:

insert  mark (sid ,c,cpp,sql)values(1001,25,63,45)

check the data after inserted

select * from mark

  If you want to learn more Details  on MSBI then read my next article,

 If you have any query  then frankly  leave mail me dotnetbyabhipatil@gmail.com

For more article  visit my blog

http://dotnetbyabhipatil.blogspot.in

          

• Tweet
• Share
• Share
• Share
• Share

About Abhijit Patil

Abhijit Patil is a Founder of Dotnetbyabhipatil ,he Passionate on Technology ,He have 3 Award on dotnetspider Including Golden. He got 2nd Time MVP(Most Valuable Professional) award at Delhi International Conference on C# Corner. also Chapter Lead of C# Corner Pune Chapter.

Related Post