Some time, we need some variable to transfer number, date or even table.
For accomplish tasks like that , I will introduce some build-in function of sql server in this instruction.
Script:
----------begin------------
----------part 1------------
declare @first_input table(lastfirst varchar(50),
student_number int)
insert @first_input
output inserted.*
select lastfirst,student_number from students
----------part 2------------
declare @second_edit table(student_name varchar(50),
student_number_bef int,
student_number_aft int)
update @first_input
set student_number=round(STUDENTS.student_number/10,0)
output
STUDENTS.LASTFIRST,
DELETED.student_number AS BEF,
inserted.student_number AS AFT
into @second_edit
FROM @first_input F,STUDENTS
WHERE f.student_number=STUDENTS.STUDENT_NUMBER
select * from @second_edit
----------part 3------------
declare @third_backup table(student_name varchar(50),
student_number_bef int,
student_number_aft int,
delete_time datetime)
delete @second_edit
output
deleted.*,GETDATE()
into @third_backup
from @second_edit
select * into #ICTbackup from @third_backup
select * from #ICTbackup
Go
------------end-------------
There are 3 parts in this script(Please don’t apart them, they are ONE)
Part 1: grab some data into a table-type variable and print them out.
Part 2: modify these data and print them out.
Part 3: delete all we have in part 1 and 2 then move them into a temporary table.
Tips:
Q: What's the diffrience between table-type variable and temporary table?
A: Let's put this in fairy tale.
Table-type variable just like the 'stupid duck' in Grimm's fairytale.
That duck only lives in a specific chapter, when you move into another story and you try to mention 'The Duck', it turns out no one knows 'The Duck'.
As for temporary table, it's like 'The little prince'.
He is a role lives through the whole book! In whichever page you say, 'the little prince', we all know who he is!
*BUT remember, all they two only lives in one book, when you jump into another book, they disappeared. (It means all they two are temporary, Table-type variable will disappear when you start a new script; Temporary table will disappear when you reconnect to database.)
*Try This:
Q: What is 'Go'?
Please try below script and see what happen.
Script:
----------begin------------
----------part 1------------
declare @first_input table(lastfirst varchar(50),
student_number int)
insert @first_input
output inserted.*
select lastfirst,student_number from students
Go –-be careful and try to remove this--
----------part 2------------
declare @second_edit table(student_name varchar(50),
student_number_bef int,
student_number_aft int)
update @first_input
set student_number=round(STUDENTS.student_number/10,0)
output
STUDENTS.LASTFIRST,
DELETED.student_number AS BEF,
inserted.student_number AS AFT
into @second_edit
FROM @first_input F,STUDENTS
WHERE f.student_number=STUDENTS.STUDENT_NUMBER
select * from @second_edit
GO –-be careful and try to remove this--
----------part 3------------
declare @third_backup table(student_name varchar(50),
student_number_bef int,
student_number_aft int,
delete_time datetime)
delete @second_edit
output
deleted.*,GETDATE()
into @third_backup
from @second_edit
select * into #ICTbackup from @third_backup
select * from #ICTbackup
Go –be careful--
------------end-------------
Drop table if you want to try this again!
USE:
Drop table #ICTbackup
