How to Use Temporary Variable

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