Saturday, June 2, 2012

Select Into, Delete Into

Select Into

We can use Into option in select and delete DML command, most of us will use select into to create a dummy table with same copy of table (excluding constraints) immediately, We can create a duplicate table wth complete structure or we can create with required fields.


select * into new_tale from old_table

select col1, col2 into new_table from old_table

Delete Into   -- Using this delete into commnad we can transfer rows into another table with in a single statement.  Below is the example

create table first (id int, name varchar(10), age int)
insert into first values (1, 'kalyan', 30)
insert into first values (2, 'kumar', 31)
insert into first values (3, 'vijay', 28)
insert into first values (4, 'hari', 25)

select * from first

create table second (id int, name varchar(10))
delete from first output, into second where id = 1

select * from second

No comments: