Differences between temporal tables and data tables in SQL Server

Source: Internet
Author: User

Differences between temporal tables and data tables in SQL Server

1, how to determine the temporary table and the data table has been generated

--how to tell if a temporary table has been created---if exists(Select *  fromTempdb.. sysobjectswhereId=object_id('tempdb: #Temp_Student'))    begin        Print 'temporary table exists'; EndElse    begin        Print 'No temporary table exists'; End--how to tell if a data table has been created---if exists(Select *  fromSys.tableswhereName='data_student')    begin        Print 'Presence Data Table'; EndElse    begin        Print 'No data tables exist'; End

Where temporary tables are created by default in tempdb (staging database) sysobjects, and data tables are created in the sys.tables of the current database

2. How to create temporary tables and data tables

When we check the temporary table, the data table does not exist, the general creation of temporary tables, data tables, when present, can be deleted or emptied data

--1, how to determine whether the temporary table has been created---if exists(Select *  fromTempdb.. sysobjectswhereId=object_id('tempdb: #Temp_Student'))    begin        --print ' exists temporary table ';        --Delete temporary table (including table structure)--        --drop table #Temp_Student        --Delete temporary tables (excluding table structure)--        truncate Table#Temp_StudentEndElse    begin        --print ' no temporary table ';        Create Table#Temp_Student (Uidint Identity(1,1)Primary Key, Ageint  not NULL, Namevarchar( -) not NULL,        )    End    --2, how to determine whether the data table has been created---if exists(Select *  fromSys.tableswhereName='data_student')    begin        --print ' presence data table ';        --Delete data table (including table structure)--        --drop table Data_student        --Delete a data table (excluding table structure)--        truncate Tabledata_studentEndElse    begin        --print ' does not exist data table ';        --when not present, create a data table--        Create Tabledata_student (Uidint Identity(1,1)Primary Key, Ageint  not NULL, Namevarchar( -) not NULL,        )    End

3. How to add data from temporary tables and data tables

-----3. How to insert data------1) Insert the data into the temporary table---Insert  into#Temp_Student (Age,name)Values( +,'Zhang San'),( A,'John Doe') --2) Insert the data into the data table---Insert  intoData_student (Age,name)Values( at,'Harry'),( -,'Zhao Liu')

4. How to query temporary tables and data table data

-- ---4, how to query the    data------- -- 1) Query the temporary table-- Select *  from #Temp_Student -- 2) Query the temporary table-- Select *  from Data_student

5. Results after query

Differences between temporal tables and data tables in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.