Can i have example of declare table and temp table in sql ? and how the update operation can be used on it ?


Can i have example of declare table and temp table in sql ? and how the update operation can be used on it with real life scenario ?

 


Asked by:- SnehalSawant
0
: 105 At:- 4/14/2018 4:48:37 PM
sql c#






2 Answers
profileImage Answered by:- vikas_jk

Syntax of creating temp table in SQL server is as below

CREATE TABLE #table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

In the above table # specifies that it is temp table

LOCAL TEMPORARY TABLES are prefixed with the # character (ie: #employees).

This #employees table is stored in tempdb and SQL Server will automatically delete this table when the SQL Server session no longer requires it

Example :

create table #TempEvent
(
    EventID int, 
    EventTitle Varchar(50), 
    EventStartDate DateTime, 
    EventEndDate DatetIme  
)

For Updating Temp Table, suppose you have two tables Table1 and table2, where table 2 is temp one, then syntax for updating would be

UPDATE t1 SET t1.sales=t2.sales
FROM table1 t1 INNER JOIN table2 t2 ON t1.MONTH=t2.MONTH

Take a look at these examples

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b7c1a66b-820f-4745-b642-d943b28a6d7f/updating-a-temporary-table-from-another-table-by-matching-column-values?forum=transactsql

https://stackoverflow.com/questions/15873234/how-to-update-temptable

Also, please read about CTE in SQL, using WITH in SQL

Hope this helps.

1
At:- 4/15/2018 6:02:59 PM


profileImage Answered by:- SnehalSawant

Thanks!!!

0
At:- 4/17/2018 2:33:00 PM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use