Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Different ways to create a table and insert data into the table

, 17 Nov 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Tables can be defined as the structure which contains the data in the database. Through this article, I to give the basic information about the different ways of creating a table

Tables can be defined as the structure which contains the data in the database. There can be many ways to create table and to insert data into the table. Some of the ways to create a table is given below:

1)Creation of a Table with the Help of a Create Statement

For example, suppose we want to create a table called tbl_student then the syntax of creating this table is given below:

CREATE TABLE tbl_Students( [Studentid] [int] IDENTITY(1,1) NOT NULL, 
    [Firstname] [nvarchar](200) , [Lastname] [nvarchar](200) , [Email] [nvarchar](100) )

Let us suppose we want to insert data into the table tbl_students, then we can use the insert statements to insert data into the table.

The insert statement used for inserting the data is given below:

Insert into tbl_students(Firstname,lastname,email) select 'Vivek','Johari',
    'Vivek@abc.com' union all select 'Pankaj','Kumar',
    'Pankaj@abc.com' union all Select 'Amit','Singh','amit@abc.com'

Here we can insert multiple rows into the table with the help of a single query mentioned above.

Another way to insert the values into the table tbl_students is :

insert into tbl_students(Firstname,lastname,
    email) Values('Manish','Kumar',
    'manish@abc.com') Insert into tbl_students(Firstname,
    lastname,email) Values('Abhishek','Singh',
    'abhishek@abc.com') Insert into tbl_students(Firstname,
    lastname,email) Values ('Uma','Sharma','Uma@abc.com')

Here, whenever we need to insert a row into the table ,we have to use a seperate insert statement.

The result of the above Sql queries can be verified with the help of the following query:

SELECT * FROM tbl_students

Result:

Note: We don't need to insert data into the column Studentid since it is defined as the Identity column and whenever a record is inserted into the table, sql server automatically insert value into this column.

2)Creation of Table with the Help of Another Table

2.1)Suppose we want to create a table tbl_studentinfo which contains a subset of the columns (studentid, Firstname, Lastname) of the table tbl_student then we can use the following query. Select studentid, Firstname, Lastname into tbl_studentinfo from tbl_students

This query will create a table tbl_studentinfo having columns studentid, Firstname, Lastname and it contains data of these columns from the table tbl_students. This can be verify with the help of the given below query.

SELECT * FROM tbl_studentinfo

Result:

2.2)Suppose we want to create a table which is exactly the copy of a given table then we can also use the following Sql query

SELECT * INTO tbl_studentscopy FROM tbl_students

The table tbl_studentscopy created by the above Sql query will contains exactly the same data as tbl_students. This can be verified with the help of the query given below:-

SELECT * FROM tbl_studentscopy

Result:

There is another interesting way to insert the values into a table with the help of another table. For example, suppose we have a table named as tbl_Studentsdemo whose structure is given below:

CREATE TABLE tbl_Studentsdemo( [Studentid] [int] IDENTITY(1,
    1) NOT NULL, [Firstname] [nvarchar](200) , [Lastname] [nvarchar](200) ,
    [Email] [nvarchar](100) )

Now if we want to insert values into the table from the table tbl_students, then we can use the following sql query to insert the data into the table.

Insert into tbl_Studentsdemo(Firstname,lastname,
    email) SELECT Firstname,lastname,email FROM tbl_students

The above query will insert all the data from the table tbl_students into the table tbl_Studentsdemo. Again we can verified it with the help of the given query:

SELECT * FROM tbl_Studentsdemo

Result:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vivek Johari
Technical Lead
India India
I am currently working as a Analyst and have around 7.5 years of experience in database.
 
Degree:-
Master Degree in Computer(MCA)
 
Work experience:-
Designing of the database.
Database Optimization.
Writing Complex Stored Procedures,Functions,Triggers etc.
Designing and developing SSIS & DTS packages.
Designing SQL Reports using SSRS.
Database Server Maintenance.
 
Certification:-
Microsoft certified Sql DBA in Sql server 2008 (MCTS).
Microsoft certified BI professional in Sql server 2008 (MCTS).
Oracle certified profession DBA in ORACLE 10g (OCP)
certified profession DBA in ORACLE 9i (OCP)
 
My other publication
Technical Blog:- Technologies with Vivek Johari
 
Moderator and Blogger at BeyondRelational.com
 
Guest Author and Blogger at sqlservercentral.com
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
Generalthanks for sharing - have 5 PinmemberPranay Rana24-Jan-11 18:38 
GeneralRe: thanks for sharing - have 5 PinmemberVivek Johari26-Feb-11 6:20 
GeneralFlaw in the Second Method Pinmembertarun_j20017-Nov-10 21:02 
GeneralRe: Flaw in the Second Method PinmemberVivek Johari2-Dec-10 17:36 
GeneralMy vote of 5 PinmemberAvinash C Dubey17-Nov-10 7:41 
GeneralRe: My vote of 5 PinmemberVivek Johari17-Nov-10 7:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 17 Nov 2010
Article Copyright 2010 by Vivek Johari
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid