Big Data Sets

Hi ,

In this article , I will tell you how to use the default value , how to copy data from another table .

USING DEFAULT VALUE

The default value specified for the table column is assigned . It tries to prevent users from entering incorrectly and can be used in Insert and Update . Here’s an example of using the default value :

  • In our current example we create a table and we will add columns such as name and surname to this table , then we will make a default column that we have chosen so that if we don’t add anything to that column , we will return the default value . You will understand more easily when you look at the pictures below 🙂

Here we created our table and set our default value .

Now we have added to our table above .

And the result :

COPYING DATA FROM ANY OTHER TABLE

With insert we can copy data from one table to another table using subqueries . But Values are not used here . The number of columns in the insert statement and the number of columns in the subquery must be the same .

  • When we say WHERE 1 = 0 , we only get the structure of the table , not the data .

As you can see above , we created a new table and when we created this table , we took the structure of admin.new_table table .

Now , we have added the person whose employee_id is equal to 6 to the table we created.

MULTI INSERT

We’ve seen just inserting data into our table with the Insert statement , and now we’ll see how to add records to multiple tables at the same time with an insert clause under this topic .

Multiple inserts are widely used in DataWarehouse systems . We can provide Conditional Insert using IF – THEN . Use of :

INSERT ALL 

INTO target_a VALUES ( ... , .....)

INTO target_b VALUES ( ... , .....)

INTO target_c VALUES ( ... , .....)

SELECT .......

FROM sourcetab

WHERE ....... ;

WHAT ARE THE MULTIPLE INSERT TYPES ?

  • UNDERSTANDING INSERT

  • CONDITIONAL INSERT ALL

  • CONDITIONAL INSERT FIRST

  • VERTICAL INSERT

UNDERSTANDING INSERT ALL

Now let’s create 2 tables from admin.new_table table and add the person named marlyn to these tables at the same time :

Now we have created our tables with conditional insert all , we want to add marlyn to both tables at the same time :

And we’re checking to see if it was last added :

CONDITIONAL INSERT ALL

CONDITIONAL INSERT FIRST

  • Now let’s create 3 experiment tables to understand this topic .

  • Let’s insert the new_table table at the same time . In doing so , according to the salary , let’s insert it into 3 .

Now let’s list the highest salary areas :

Now the areas between the two salary values are :

And finally, to see salaries below 4500 :

VERTICAL INSERT

  • Let’s create a table named SALES_SOURCE .

  • And create a table named SALES_INFO .

  • Now add to the SALES_SOURCE table :

  • Let’s insert the data of sales_source table into the sales_info table by making vertical insert :

  • We check the sales_info table to check :

MERGE

Provides conditional execution of insert , update, delete operations in a table . If the corresponding row is present in the table , update becomes insert . Used in DataWirehouse applications . And it saves us from updating separately .

  • Create a merge_test table from the NEW_TABLE table .

  • And finally we run the following query to see the merge_test table .

  • We will see that the records in the new_table table are added to the merge_test table .

See you in my next post.