Trending February 2024 # Learn The Examples Of Truncate Table Statement # Suggested March 2024 # Top 6 Popular

You are reading the article Learn The Examples Of Truncate Table Statement updated in February 2024 on the website Achiashop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Learn The Examples Of Truncate Table Statement

Introduction to SQL TRUNCATE()

TRUNCATE in standard query language (SQL) is a data definition language (DDL) statement used to delete complete data from a database table without deleting it. It frees up space or empties space in the table. However, we should note that TRUNCATE TABLE statements might need to be roll backable in many SQL databases. Also, being a DDL statement, the TRUNCATE table statement does not require a commit at each step; it automatically fires a commit at the end of the execution of the statement. Hence, we should be careful while using it.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and Parameters

The basic syntax for using a SQL TRUNCATE TABLE statement is as follows :

TRUNCATE TABLE table_name;

Table_name: It is the name of the table whose records or rows you want to delete permanently.

How does the TRUNCATE TABLE statement work in SQL?

TRUNCATE TABLE statement in SQL works by zeroing out a file in the database, i.e., after running a TRUNCATE statement on an existing table, the table becomes empty and hence does not hold any row records. It resets the table to zero entries.

However, its structure, columns, indexes, constraints, relationships, views, etc., are preserved after truncating the table. The entire operation is like erasing data from the table but keeping the table intact.

TRUNCATE in Data Definition Language (DDL) is equivalent to DELETE in Data Manipulation Language (DML). The only difference is that the latter can be rolled back, but the first cannot. However, TRUNCATE is faster than DELETE because it usually bypasses the transaction system. It is not logged (it can vary across SQL databases) and does not follow predicates and hence seems to be faster than the DELETE operation. DELETE is a safer and slower operation.

Examples of SQL TRUNCATE()

Here are a few examples to explain the TRUNCATE TABLE statement in great detail.

Example #1

Simple SQL query to illustrate the function of the TRUNCATE TABLE statement.

To understand the SQL TRUNCATE TABLE, let us consider a “customers” table. The data in the table looks like this.

Command:

SELECT * FROM public.customers

Output:

Next, let us run the TRUNCATE TABLE statement on the customer’s table to remove all its records. We can do so using the following SQL query.

Command:

TRUNCATE TABLE customers;

Output:

We can see in the figure below that the TRUNCATE TABLE statement has removed all the records in the customer’s table. However, all the columns, relationships, indexes, and table structures have been kept safe.

Command:

SELECT * FROM customers;

Output:

Example #2

For this, let us consider two tables, “customer_details” and “students”. The table structure and the data in them look something like this. Records in the “Customer_details” table are as follows:

Command:

SELECT * FROM public.customers_details

Output:

Records in the “Students” table are as follows:

SELECT * FROM public.students

Output:

Next, we will run the TRUNCATE TABLE on the customer_details table and DROP TABLE on the student’s table, and then we will check the difference.

Command:

TRUNCATE TABLE customer_details;

Output:

Command:

Output:

We can observe from the images above that the DROP TABLE statement is faster than the TRUNCATE TABLE statement in SQL.

Now let us check what happened to the two tables after truncating and dropping, respectively.

Command:

SELECT * FROM customer_details;

Output:

Command:

SELECT * FROM students;

Output:

From the above two images, we can observe that in the TRUNCATE statement, the table structure is preserved; only the data/records in the table have been removed. Whereas in the case of the DROP TABLE statement, the entire table has been removed from the database.

Conclusion

TRUNCATE TABLE in SQL is a Data Definition Language (DDL) statement that empties an existing table by removing all the records while preserving table columns, privileges, indexes, views, constraints, relationships, etc. It is equivalent to but faster than the DELETE statement in SQL. However, unlike DELETE, it cannot be rolled back.

Recommended Articles

We hope that this EDUCBA information on “SQL TRUNCATE()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

You're reading Learn The Examples Of Truncate Table Statement

Cassandra Table Example: Create, Alter, Drop & Truncate Table

The syntax of Cassandra query language (CQL) resembles with SQL language.

How to Create Table in Cassandra

Column family in Cassandra is similar to RDBMS table. Column family is used to store data.

Command ‘Create Table’ is used to create column family in Cassandra.

Syntax Create table KeyspaceName.TableName ( ColumnName DataType, ColumnName DataType, ColumnName DataType . . . Primary key(ColumnName) ) with PropertyName=PropertyValue;

1. Primary key: There are two types of primary key.

Single Primary Key: Single primary key is specified by the following syntax.

Syntax Primary key (ColumnName)

In the single primary key, there is only a single column. That column is also called partitioning key. Data is partitioned on the basis of that column. Data is spread on different nodes on the basis of the partition key.

2. Compound Primary Key: Compound primary key is specified by the following syntax.

Syntax Primary key(ColumnName1,ColumnName2 . . .)

In above syntax, ColumnName1 is the partitioning key and ColumnName2 is the Clustering key. Data will be partitioned on the basis of ColumnName1 and data will be clustered on the basis of ColumnName2. Clustering is the process that sorts data in the partition.

3. Compound Partitioning key: Compound partitioning key is specified by the following syntax.

Syntax Primary Key((ColumnName1,ColumnName2),ColumnName3...))

In above syntax, ColumnName1 and ColumnName2 are the compound partition key. Data will be partitioned on the basis of both columns ColumnName1 and ColumnName2 and data will be clustered on the basis of the ColumnName3. If you have too much data on the single partition. Then, compound partitioning key is used. Compound partitioning key is used to create multiple partitions for the data.

With Clause

“With clause” is used to specify any property and its value for the defined table. For example, if you want to compress Cassandra table data. You can set compression property by specifying compression algorithm property value in “With clause.”

Example

Here is the execution of the command ‘Create table’ that will create table name ‘Student’ in the keyspace ‘University.’

After successful execution of the command ‘Create table’, table ‘Student’ will be created in the keyspace ‘University’ with columns RollNo, Name and dept. RollNo is the primary key. RollNo is also a partition key. All the data will be in the single partition.

Cassandra Alter table

Command ‘Alter Table’ is used to drop column, add a new column, alter column name, alter column type and change the property of the table.

Syntax

Following is the syntax of command ‘Alter Table.’

Alter table KeyspaceName.TableName + With propertyName=PropertyValue Example

Here is the snapshot of the command ‘Alter Table’ that will add new column in the table Student.

After successful execution of the command ‘Alter Table’, a new column ‘Semester’ with ‘int’ data type will be added to the table Student.

Here is the screenshot that shows the updated Student table.

Cassandra Drop Table

Command ‘Drop table’ drops specified table including all the data from the keyspace. Before dropping the table, Cassandra takes a snapshot of the data not the schema as a backup.

Syntax Drop Table KeyspaceName.TableName Example

Here is the snapshot of the executed command ‘Drop Table’ that will drop table Student from the keyspace ‘University’.

After successful execution of the command ‘Drop Table’, table Student will be dropped from the keyspace University.

Here is the snapshot that shows the error returned by the Cassandra when tried to access Student table that does not exist.

Cassandra Truncate Table

Command ‘Truncate table’ removes all the data from the specified table. Before truncating the data, Cassandra takes the snapshot of the data as a backup.

Syntax Truncate KeyspaceName.TableName Example

There are three records in the table Student. These are the records in the table.

Here is the snapshot of the executed command ‘Truncate table’ that will remove all the data from the table Student.

After successful execution of the command ‘Truncate Table’, all the data will be removed from the table Student.

Here is the snapshot of the database state where there are no records in the table Student.

Learn The Examples Of Sql Datediff()

Introduction to SQL Datediff()

In SQL server suppose we have dates in our data and we want to know the difference between those dates then we can use the DATEDIFF function to know the difference between those dates in days, months, or years. So this function returns an integer as output and to understand more about this function lets know it’s syntax first.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of Datediff() in SQL DATEDIFF (interval, startdate, enddate)

1. Interval – This is also called datepart and it is provided as a string to this function. This argument can be anything that represents a time interval like a month, week, day, year. We can also specify the quarter of the year.

year, yyyy, yy = Year SELECT DATEDIFF(year, '2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); quarter, qq, q = Quarter SELECT DATEDIFF(quarter,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); month, mm, m = month SELECT DATEDIFF(month, '2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); dayofyear = Day of the year SELECT DATEDIFF(dayofyear,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); day, dy, y = Da SELECT DATEDIFF(day,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); week, ww, wk = Week SELECT DATEDIFF(week,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); hour, hh = hour SELECT DATEDIFF(hour,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); minute, mi, n = Minute SELECT DATEDIFF(minute,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); second, ss, s = Second SELECT DATEDIFF(second,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); millisecond, ms = Millisecond SELECT DATEDIFF(millisecond,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); microsecond, mcs = Microsecond SELECT DATEDIFF(microsecond,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

2. startdate, enddate – These are the actual dates to get the difference between. This is a mandatory parameter.

This function works in the SQL server starting from the 2008 version, Azure SQL Data Warehouse, Azure SQL Database, Parallel Data Warehouse.

Return Value

The return value is an int and is expressed by the datepart or the interval boundary which is the difference between the start and end date.

If the range of the return value for int is out of[-2,147,483,648 to +2,147,483,647], DATEDIFF function returns an error. The max difference between the start and end date is 24 days, 20 hours, 31 minutes, and 23.647 seconds for the millisecond. The max difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds for the second.

If the start and end date have a date with different data type then DATEDIFF will set 0 the missing parts of the other date which has lower precision

The above queries have the same start and end values. These are adjacent dates and the difference between them is a hundred nanoseconds (.0000001 second). The start and end dates cross one calendar and the result of each query is 1.

Examples

Here are the examples mention below

Example #1 – Calculating Age select ID,emp_name,emp_dateOfBirth from Employee

We have the above table Employee which consists of the date of birth and from this, we will calculate the age in terms of a year, month, and days in 2 steps

Step 1: Creating a function

CREATE FUNCTION fnEmpComputeAge(@EmpDOB DATETIME) RETURNS NVARCHAR(50) AS BEGIN DECLARE @AgeTempdate DATETIME, @AgeYears INT, @AgeMonths INT, @AgeDays INT SELECT @AgeTempdate= @EmpDOB SELECT @AgeTempdate=DATEADD(YEAR, @AgeYears, @AgeTempdate) SELECT @AgeTempdate=DATEADD(MONTH, @AgeMonths, @AgeTempdate) SELECT @AgeDays=DATEDIFF(DAY, @AgeTempdate,GETDATE()) DECLARE @EmpAge NVARCHAR(50) SET @EmpAge=Cast(@AgeYears AS NVARCHAR(4))+' AgeYears '+Cast(@AgeMonths AS NVARCHAR(2))+' AgeMonths '+Cast(@AgeDays AS NVARCHAR(2))+' AgeDays Old' RETURN @EmpAge End

In the above example, we have created a SQL Function to calculate the age of the employee from the DOB so the function takes @EmpDOBas a parameter and returns NVARCHAR(50). We will see this in action when we run this function. In step, we have created this function.

Then we add the calculated years in the @AgeTempdate using the DATEADD function.

Similarly, we calculated the month and added in @AgeTempdate, and then it is used to calculate days. Next, we declared @EmpAge and set it to the concatenation of the final output. Since the calculation result is in int we used Cast function to convert it into nvarchar.

Step 2: Using the function in the query

select ID,emp_name,emp_dateOfBirth,dbo.fnEmpComputeAge(emp_dateOfBirth) as EmpAge from Employee

The result is as follows:

As we can see we have used dbo.fnEmpComputeAge function and passed emp_dateOfBirth to calculate EmpAge and the result is as above.

Example #2 – Using scalar functions and subqueries for start and end date SELECT DATEDIFF(day, (SELECT MIN([ShipDate])FROM Sales.SalesOrderHeader), (SELECT MAX([ShipDate])FROM Sales.SalesOrderHeader)) as ShippingDateDiff;

The result is as follows:

In this example, we have calculated the shipping date difference using scalar functions and scalar subqueries for min and max.

Example #3 – Using ranking functions for the start date argument SELECT FirstName as first_name,LastName as last_name, DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY DepartmentName),SYSDATETIME()) AS row_number FROM dbo.DimEmployee;

The result is as follows:

In this function, we have used ROW_NUMBER() ranking function as the start date argument.

Example #4 – Using an aggregate window function for the start date argument SELECT FirstName as first_name,LastName as last_name,DepartmentName as department_name, DATEDIFF(year,MAX(HireDate) OVER (PARTITION BY DepartmentName),SYSDATETIME()) AS HireInterval FROM dbo.DimEmployee

Conclusion

Hopefully, now you know what DATEDIFF() is in the SQL server and how it is used to calculate results the difference between date according to datepart.

Recommended Articles

We hope that this EDUCBA information on “SQL DATEDIFF()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Learn The Different Examples Of Plsql Pivot

Introduction to PLSQL pivot

PL/SQL provides the different types of functionality to the user; the pivot is the one type of functionality that is provided by the PL/SQL. Basically, we call it a pivot clause. By using pivot clauses, we cross table query as per requirement; in another way, we can combine, or we can aggregate our result from rows into the columns as per our requirement. Basically, the pivot clause introduced in Oracle 11g and the pivot returns more than one column after the execution. By using pivot clauses, we can combine the difference into a single result and generate the required output.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax select * from (select colm name 1, colm name name N from specified table where conditional expression) PIVOT

Explanation

First, we use the select clause to retrieve the records from the specified table. Inside the select, we write the subquery with a column name, and here we specify where clause with conditional expression.

Specified aggregate function name: it is used to specify the aggregate function name that we can write any function name such as SUM, MIN, MAX, etc.

IN (specified expression 1, specified expression 2,…..specified expression N): It is used to hold the list of column name 2 that values to pivot into the cross table.

Specified subquery: Basically, it is used instead of list values, and the output of the subquery would be utilized to calculate the values for column 2 in the cross-tabulation query output, which would then be translated to headings in this syntax.

How does pivot work in PL/SQL?

Now let’s see how pivot clauses work in PL/SQL as follows.

Let’s see the different ways to implement the pivot clause as follows.

Specify the Group Columns:

Any columns not stated in the FOR clause are utilized as a part of the Oracle PIVOT GROUP BY when employing the PIVOT keyword. The only column in the previous example was the location column, which was fine.

We can also use the Where clause with pivot clause:

The results of our searches above provide a pivoted summary of all data. A few fields are used to aggregate all of the entries, and the SUM of the selling amount is displayed.

What if you want to limit it to just a few rows?

Like a regular SELECT query, you may use a WHERE clause. Then we will get the error due to incorrect syntax, so we need to write the correct syntax that means the PIVOT clause must appear after the WHERE clause; this is the case.

Now let’s see how we can use Aliasing in the pivot column:

The column headings will be shown as the table’s column name in the queries we’ve looked at so far. What if you want to call them something else? A column alias can be specified using the PIVOT keyword. Both the pivot clause and the pivot in a clause can be used for this.

Now let’s see how we can perform the multiple aggregations in pivot:

We can make the group of multiple columns:

This is another way to implement the multiple columns into the pivot statement to group by multiple columns as per our requirement.

We can implement pivot with XML as follows:

You may display your findings in an XML format using the PIVOT keyword. It’s as simple as following the PIVOT keyword with the XML keyword.

Examples of PLSQL pivot

Now let’s see the different examples of pivot clauses in PL/SQL for better understanding as follows.

First, we need to create the table by using the following create table statement as follows.

create table stud(roll_no integer not null, name varchar2(50), dept_id integer not null, primary key(roll_no));

Explanation

By using create table statement, we created a new table name as a stud with different attributes such as roll_no, name, and dept_id with different data types, and in this example, the primary key is roll_no. The final output of the above statement we illustrated by using the following screenshot as follows.

insert into stud(roll_no, name, dept_id) values(1,'Jenny',10); insert into stud(roll_no, name, dept_id) values(2,'Jenny',10); insert into stud(roll_no, name, dept_id) values(3,'Jenny',20); insert into stud(roll_no, name, dept_id) values(5,'Sameer',20); insert into stud(roll_no, name, dept_id) values(6,'Sameer',10); insert into stud(roll_no, name, dept_id) values(7,'Sameer',20);

Explanation

In the stud table, we inserted a total of 6 records by using the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

If records are not in order, then we can use order by clause to make the records in order. In this example, all records we order by roll_no, as shown in the above screenshot.

Now implement the pivot clause that means write the cross table subquery as follows.

select * from (select name, dept_id from stud) pivot (count(dept_id) for dept_id IN (10, 20, 30)) order by name;

Explanation

In the above example, we write the two different queries that we call subquery and merge by using the pivot clause. Now let’s see how it works. In the above example, we first decide which field we want to add in the pivot clause; here, we add name and dept_no. After that, we need to specify the column in any order that we want. The next part of the query, it contains the aggregate function and pivot value that we want, as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

Conclusion – PLSQL pivot

We hope from this article you learn PL/SQL pivot. From the above article, we have learned the basic syntax of the pivot, and we also see different examples of the pivot. From this article, we learned how and when we use PL/SQL pivot.

Recommended Articles

We hope that this EDUCBA information on “PLSQL pivot” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Learn The Use Cases For Return Statement

Introduction to MATLAB Return

In computer programming, a return statement is defined to be executed to return the control to the parent sub routine from the invoking subroutine. The flow of execution resumes at the point in the program code immediately after the instruction, which is called its return address, and the running scope of the code can be defined as the called subroutine. In the absence of a parent subroutine, the return statement returns the control of execution to the command prompt. In this topic, we are going to learn about Matlab return.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

The return command redirects the control to invoking script or function instead of letting MATLAB execute the rest of the consecutive commands in the called subroutine. MATLAB redirects the control nothing but to the command prompt when a script or a function contains a return, and it is not being called from any invoking program.

Use cases for a return statement

Return command is used in different conditions based on the technical requirement of the program. It can also be used for data validity checking functionality. Some of the important use cases are discussed below.

1. Bringing control to keyboard

If a program needs the user to take action on the occurrence of some specific condition, the current subroutine or function can be called directly without being triggered by any parent sub routine, and the flow of control returns to the command prompt or the keyboard when the command ‘return’ is executed.

Example:

The below code snippet defines a function findindex() where the return command is used with 2 purposes:

Performing validation checking on input data

Returning control to keyboard once the match is found

endfunction

Case 1: The return statement is executed on a negative input being given

findindex(-15,[12 34 54 15 32])

Output:

Case 2: The return statement is executed on match to the input data is found

findindex(15,[12 34 54 15 32])

Output:

2. Redirecting execution flow to the parent (calling) subroutine from the called subroutine

If the program needs to reroute the flow of control to the calling subroutine or the calling function on the occurrence of some specific condition. It can be carried out when its parent subroutine triggers the current in the current subroutine or function, and the command ‘return’ is executed.

Example:

The below code snippet defines a function findindex() within another function callfunction() where the return command is used with 2 purposes:

Performing validation checking on input data of findindex() function

Returning control to callfunction() from findfunction() return command

function resultfunc = callfunction(inputval,referenceArray)result=findindex(inputval,referenceArray); if isnan(result)disp(‘Match is not found.’)    elsedisp([‘Match is found at ‘ num2str(result)])    endendfunction

callfunction(-12, [10 21 14 15 20 12 20])

Case 2: The return statement is executed on match to the input data is found

callfunction(12, [10 21 14 15 20 12 20])

Output:

3. Usage of return and continue statement in a loop

The program can have the flexibility to decide on which condition the flow of control should be rerouted to its calling sub routine or the command prompt and on which condition will force the flow to stay in the current system.

Example:

The below code snippet defines a function findindex() within another function callfunction() where the return command is used with 2 purposes:

Performing validation checking on input data of findindex() function

Returning control to callfunction() from findfunction() return command when the match is found and make the flow stay within the loop using the command ‘continue’ when the matched element is not found.

Example:

endfunction

findindex(-15,[12 34 54 15 32])

 Output:

Case 2: The return and continue statement execution based on finding matched or non-matched element

findindex(15,[12 34 54 15 32])

Output:

Advantages of Matlab return

Using a return statement prevents the execution of unwanted functionalities once the desired condition is satisfied. As a result, it improves code quality and optimizes the code execution. As it reduces the number of instructions to be executed, it also reduces the execution time for the program. Thus it

makes the execution faster and results in improving the performance. Use of return statement in association with ‘continues’ statement provides flexibility to the program to decide whether to reroute the flow of control or keep it running within the current scope of the code.

Additional note

While using return within conditional blocks, such as if or switch, or within loop control statements, such as, for or while, the programmer needs to be careful. In MATLAB, when the control flow reaches a return statement in a conditional block, it just exits the loop and exits the script or function in which the return command is executed. Hence directly, it returns control to the invoking subroutine or commands prompt.

In MATLAB, it is not supported to return values using the return statement. To send a return value, it is required to set the value of each ‘out’ arg. Functions may return more than one argument as return values.

Recommended Articles

This is a guide to Matlab return. Here we discuss the Use cases for the return statement along with the examples, cases and outputs. You may also have a look at the following articles to learn more –

The Table Keyword In Dax Studio: Basic Examples

In this tutorial, you’ll learn about the TABLE keyword in DAX Studio. The TABLE keyword allows you to create tables inside your DAX query.

This is a continuation of a series of tutorials on the different keywords that you can use when creating DAX queries. Before diving into this topic, make sure to read first on the DEFINE and MEASURE keywords.

To use this keyword, first write DEFINE followed by TABLE. Then, provide the name of the table you want to create. In this example, the table’s name is ModelStats.

A specific function is used for this query: the COLUMNSTATISTICS () function. This function can be used to quickly create metadata on every table in your data model. This function is not available in the DAX in Power BI; it’s entirely unique to DAX Studio.

To view the table, write EVALUATE ModelStats.

After you run this query, you’ll get a table showing all the tables and statistics of each table in your data model.

You can also add another column by using the ADDCOLUMNS function. In this case, the column’s name is “Random” and it shows random numbers generated by DAX Studio using the RAND function.

Let’s go into a more realistic example. This is the same example used in the MEASURE keyword tutorial. It’s focused on a hypothetical business with “trendy” and “boring” products.

In this case, the goal is to segregate the Products table into 2 categories. The first table is for the TrendyProducts, while the second is for the BoringProducts.

For the TrendyProducts table, first DEFINE what are TrendyColors. In this instance, they’re Red, Blue, and Pink. Then, you need to inject that filter into the filter context. To do so, you need to use the CALCULATETABLE function.

Notice that the VAR function is used. This is to differentiate between the variables and the name of the table.

Next, create a variable for the Result. For this variable, create a new column using the ADDCOLUMNS function and name it “Description.” The Description column will identify which rows belong to the Trendy Products. Then, RETURN the Result.

You can see that the table is returning 383 rows that are marked as Trendy Products.

Now the same logic also applies for the BoringProducts table. You can copy the code and paste it after RETURN.

So instead of TABLE TrendyProducts, replace it with TABLE BoringProducts. For the CALCULATETABLE argument, write the NOT function. And then, change the column name to “Boring.”

Next, EVALUATE the BoringProducts table to view it.

You can see that the boring products return 2,134 rows. You can also see in the Description column that it only contains “Boring.”

The next thing you can do is join these two tables together using the UNION keyword.

Now, one would think that you can just write a new TABLE keyword with the UNION function to combine the two tables together.

However, it isn’t possible for this case since the BoringProducts code contains the TrendyProducts table. If you attempt to run this query, you’ll get an error.

You can’t use a query table within another query table in DAX Studio.

Instead, you should place the UNION syntax after EVALUATE.

If you run this, you’ll get a table containing both the Trendy and Boring products. You can see that this table contains 2517 rows.

This next example shows how to create a Dates table in your data model. Open a new blank query. Before anything else, let’s first try out the TABLE keyword with the CALENDAR and DATE functions. This query is simply evaluating the dates in between January 1, 2007 and December 31, 2007.

You can see that the results show all the dates in between what was specified in the query. To create more columns in the Dates table, use the GENERATE function over the current CALENDAR code. Then, use the ROW function to segregate different data within the Dates table.

The [Date] column used in this query is from the CALENDAR function. Notice also that a variable VAR CurrentDate is used. This variable stores the value that’s being accessed from the row context. That value is then returned inside the row function.

This is done to simplify the code. So instead of using the [Date] column reference, you can use the variable you declared. You can add more columns in your Dates table according to your needs.

Another thing you can do with the Dates table you created is adding in the SUMMARIZECOLUMNS function.

After EVALUATE, use SUMMARIZECOLUMNS and then COUNTROWS to count the number of rows belonging to your Calendar Year Number.

After you run this, you can see that the table reports 365 rows belong to the year 2007. You can try and experiment with your current query.

For instance, you can change the upper bound of the end date from 2007 to 2009. If you run this, you’ll see that the table now shows rows for the years 2007, 2008, and 2009.

If for example, you want to add another column that shows the first date of the table, use the FIRSTDATE function.

Similarly, use the LASTDATE function to identify the last date of each row.

To get the Total Rows in your Dates tables, use the CALCULATE function with COUNTROWS. And then, use REMOVEFILTERS to remove the filter context from the Dates table you created using SUMMARIZECOLUMNS.

After running the query, you can see that there’s a new column showing the total count of rows available in the Dates table.

Along with DEFINE and MEASURE, the TABLE keyword is vital when creating queries in DAX Studio. It helps simplify the process of creating tables.

This tutorial shows basic examples of how to use the TABLE keyword. It’s important to learn the basics as this helps in understanding more complex queries which are more common when real-world applications are involved.

Enterprise DNA Experts

Update the detailed information about Learn The Examples Of Truncate Table Statement on the Achiashop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!