SQL Server Query to Create Table and Insert Records

SQL Server Query to Create Table and Insert Records

Lets see how to create a table and also inserting the data to table as single query.

Here we are creating student table with fields id, name, class, exam date, mark1, mark and department.

Inserting 4 student details using insert command using begin and end statements once table is created.

CREATE TABLE student (
id INT PRIMARY KEY,
sname VARCHAR(10),
class VARCHAR(9),
exam_date DATETIME,
mark1 NUMERIC(7,2),
mark2 NUMERIC(7,2) NULL,
dept INT)
begin
insert into student values
    (1,'Student1','1','12-17-2018',80,NULL,2)
	insert into student values
    (2,'Student2','1','10-15-2018',90,70,3)
	insert into student values
    (3,'Student3','1','11-12-2018',85,81,3)
	insert into student values
    (4,'Student4','1','12-19-2018',60,NULL,2)
end

Output:

    (1 row(s) affected)
    

    (1 row(s) affected)
    

    (1 row(s) affected)
    

    (1 row(s) affected)

Checking database table 'student' using select query,

select * from student;

Output:

 id	sname	class	exam_date           	mark1	mark2	dept
1	Student1	1	2018-12-17 00:00:00.000	80.00	NULL    	2
2	Student2	1	2018-10-15 00:00:00.000	90.00	70.00   	3
3	Student3	1	2018-11-12 00:00:00.000	85.00	81.00   	3
4	Student4	1	2018-12-19 00:00:00.000	60.00	NULL    	2 

Lets see the another example to create department table with department entries,

CREATE TABLE dept (
id INT NOT NULL,
dname VARCHAR(14),
student_count INT NULL)
begin
insert into dept values (1,'SCIENCE',50)
insert into dept values (2,'MATHEMATICS',40)
insert into dept values (3,'ACCOUNTS',65)
end

Output:

    (1 row(s) affected)
    

    (1 row(s) affected)
    

    (1 row(s) affected)

Checking database table 'dept' using select query,

select * from dept;

Output:

id	dname	student_count
1	SCIENCE     	50
2	MATHEMATICS 	40
3	ACCOUNTS    	65