Insert a Row into Table
Basic Insert Example:
insert into employee (name,dept,salary)values('rahul','sale',49000)
Id is identity column, so no need to insert value for identity column it takes default value as defined.
details are available in data definition section.
How to see the Inserted value in table:
insert into employee (name,dept,salary)
output inserted.[name],inserted.salary
values('vimla','hr',39000),('deepanker','it',55000)
-- Inserting multiple value as well
Insert Explicit value in identity column:
insert into employee (id,name,dept,salary)output inserted.[name],inserted.salary values(23,'vimal','sale',29000)
To insert explicit value for identity column used below code:
set identity_insert employee on
Insert into Select:
Insert data from one table to another:
--Truncate table employeenew
insert into employeenew select [name],salary,dept from employee where salary<50000
Insert Top N rows:
insert top(3) into employeenew select [name],salary,dept from employee
Insert Top N Percent rows:
insert top(10) percent into employeenew select [name],salary,dept from employee
Update: We do use update statement to modify existing data in table.
update employee set state='karnatka',city='Bengaluru'
where dept='it'
Update with Join:
update employee set dept_head=d.head
from employee e join department d
on e.dept=d.dept_name
Delete: delete one or more rows of a table.
--insert into [dbo].[employeenew] select [name],salary,dept from employee
delete from [dbo].[employeenew]
where dept='it'
delete top (2) from [dbo].[employeenew]
Delete top 10 percent rows:
delete top (2) percent from [dbo].[employeenew]
delete from [dbo].[employeenew]
- The source table has some rows that do not exist in the target table. In this case, you need to insert rows that are in the source table into the target table.
- The target table has some rows that do not exist in the source table. In this case, you need to delete rows from the target table.
- The source table has some rows with the same keys as the rows in the target table. However, these rows have different values in the non-key columns. In this case, you need to update the rows in the target table with the values coming from the source table.
--truncate employeenew
merge employeenew t using employee s on
t.name=s.name
when matched
then update set t.dept=s.dept,t.salary=s.salary
when not matched by target
then insert(name,dept,salary)values(s.name,s.dept,s.salary)
when not matched by source
then
delete;