How to Generate SQL INSERT Statements from Existing Data in Oracle

Use this following format/query for generate script for insert data into database (Table) .When you export data or migrate data from one database to other database it may help you.With out creating the dumps you can export data from one database to other database.But it is table wise.

You can simply generate script for insert  and then run the generated script on command line.

Example 1 :-

 SELECT 'INSERT INTO DUAL VALUES ('''||dummy||''');' FROM DUAL;

Generated Output: INSERT INTO DUAL VALUES ('X');

Example 2 :-

SELECT 
    'INSERT INTO EMP_DETAILS VALUES (' ||
    '''' || REPLACE(EMP_NAME, '''', '''''') || ''', ' ||
    '''' || EMP_SEX || ''', ' ||
    '''' || TO_CHAR(EMP_JOIN_DT, 'DD-MON-YYYY') || ''');' AS insert_script
FROM M_EMPLOYEE;


Generated Output:

INSERT INTO EMP_DETAILS VALUES ('Rakesh', 'M', '03-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Manoj Kumar', 'M', '06-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Santosh Kumar', 'M', '02-JAN-2005');
INSERT INTO EMP_DETAILS VALUES ('Rakesh Kumar', 'M', '05-JAN-2005');
INSERT INTO EMP_DETAILS VALUES ('Sunil Dev', 'M', '01-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Sheeba', 'F', '01-JAN-2005')

Use the generated output in command line and execute.

MySQL: LIMIT Not Working as Expected

I have a Case Study!

I received a question about why the MySQL LIMIT clause was not working as expected. However, the behavior was correct according to the MySQL documentation.

The LIMIT clause can be used in the following format:

LIMIT offset, row_count

The first parameter specifies the starting position. The first record starts at position 0.

The second parameter specifies the number of records to return, not the position of the last record.

Example 1

LIMIT 0, 10

This returns 10 records starting from position 0, that is, positions 0 through 9.

Example 2

LIMIT 10, 20

This returns 20 records starting from position 10, that is, positions 10 through 29.

Example 3

LIMIT 10, 10

This returns 10 records starting from position 10, that is, positions 10 through 19.

For example:

SELECT *
FROM dbName.emp
LIMIT 10, 10;

The query returns 10 records, starting from the record at position 10.

SQL:Show Execution Plan of SQL Statement--SHOWPLAN_ALL

SQL Server: Displaying the Execution Plan Using SHOWPLAN_ALL

SET SHOWPLAN_ALL can be used to display the execution plan of a Transact-SQL statement without actually executing the statement.

Syntax:

SET SHOWPLAN_ALL { ON | OFF }

Example:

SET SHOWPLAN_ALL ON;

CREATE TABLE TEST_TABLE
(
    T_ID INT,
    T_NAME VARCHAR(50)
);

SET SHOWPLAN_ALL OFF;

When SET SHOWPLAN_ALL is ON, SQL Server does not execute the Transact-SQL statements. Instead, it returns detailed information about how the statements would be executed, along with estimated resource requirements.

For example, if you execute a CREATE TABLE statement while SHOWPLAN_ALL is ON, the table is not actually created. SQL Server only returns the execution-plan information for that statement.

After checking the execution plan, turn SHOWPLAN_ALL OFF:

SET SHOWPLAN_ALL OFF;

The subsequent statements will then execute normally.

Example with SELECT:

SHOWPLAN_ALL ON:

SET SHOWPLAN_ALL ON;

SELECT * FROM TEST_TABLE;

SHOWPLAN_ALL OFF:

SET SHOWPLAN_ALL OFF;

SELECT * FROM TEST_TABLE;

When SHOWPLAN_ALL is ON, SQL Server returns information for each subsequent Transact-SQL statement without executing it. The output is returned as a set of rows that represents the execution steps used by the SQL Server query processor.

The output contains information about the operators involved in executing the statement and their estimated resource requirements.

Important:

SET SHOWPLAN_ALL must be the only statement in a batch. It cannot be specified inside a stored procedure.

If you need more readable execution-plan output, SQL Server also provides:

SHOWPLAN_TEXT

SHOWPLAN_XML

SHOWPLAN_ALL

SHOWPLAN_TEXT provides a text-based execution plan, while SHOWPLAN_XML returns the execution plan in XML format.

Parallel

The execution-plan output can also contain information about whether an operator is executed in parallel.

A value of 0 means the operator is not running in parallel.

A value of 1 means the operator is running in parallel.

The Parallel value does not indicate whether the query was successfully executed. When SHOWPLAN_ALL is ON, the statement is not actually executed.

Note:

Use SHOWPLAN_ALL carefully, especially when testing statements that create, modify, or delete database objects, because those statements will not actually be executed while SHOWPLAN_ALL is ON.

MySQL: Use of the LIMIT Keyword

MySQL: Use of the LIMIT Keyword

The LIMIT keyword is used to restrict the number of rows returned by a MySQL query. There are several situations where LIMIT can help MySQL reduce unnecessary processing.

1. LIMIT with ORDER BY

If you use LIMIT row_count with ORDER BY, MySQL can stop sorting once it has found the required number of rows instead of sorting the complete result. If the ordering can be done using an index, this can be very fast.

2. LIMIT with DISTINCT

When LIMIT is combined with DISTINCT, MySQL stops processing once it finds the required number of unique rows.

3. LIMIT with GROUP BY

In some cases, GROUP BY can be resolved by reading the key in order and calculating the required summaries. LIMIT can prevent MySQL from calculating unnecessary GROUP BY values.

4. LIMIT and Query Execution

Once MySQL has sent the required number of rows to the client, it can stop processing the query unless SQL_CALC_FOUND_ROWS is being used.

5. LIMIT 0

LIMIT 0 quickly returns an empty result set. This can be useful for checking whether a query is valid.

Example

SELECT * FROM dbName.emp LIMIT 0;

LIMIT 0 can also be useful with MySQL APIs when you need to determine the types of the result columns.

Example

SELECT * FROM dbName.emp LIMIT 10;

The above query retrieves only 10 records from the emp table.

Sometimes we need to retrieve records in batches. For example, if the emp table contains 100 records and we want to retrieve 10 records at a time, we can use LIMIT with an offset.

SELECT * FROM dbName.emp LIMIT 0, 10;

SELECT * FROM dbName.emp LIMIT 10, 10;

SELECT * FROM dbName.emp LIMIT 20, 10;

The first value specifies the starting position, and the second value specifies the number of records to return.

Case Study: Understanding LIMIT

I came across a question where someone felt that LIMIT was not working correctly. The issue was actually with understanding the meaning of the two parameters.The first parameter indicates the starting record. 

The first record starts at position 0. The second parameter specifies the number of records to return, not the ending record.

For example:

LIMIT 0, 10

This returns 10 records starting from position 0, which corresponds to records 0 through 9.

LIMIT 10, 20

This returns 20 records starting from position 10, which corresponds to records 10 through 29.

LIMIT 10, 10

This returns 10 records starting from position 10, which corresponds to records 10 through 19.

Example:

SELECT * FROM dbName.emp LIMIT 10, 10;

In short, remember:

LIMIT offset, row_count

The first value is the starting position, and the second value is the number of rows to return.

SQL Server: Finding Nth Highest Salary

This is one of the interesting SQL queries I came across when I was asked to find the 4th highest salary. Using a sub query, we can first get the top 4 salaries and then find the 4th highest value from them. It is a simple example of how sub queries can be used to solve common SQL problems. 

 

#Select 4th Highest Salary – Method 1

SELECT TOP 1 E_ID
FROM
(
    SELECT DISTINCT TOP 4 E_ID
    FROM EMP
    ORDER BY E_ID DESC
) A
ORDER BY E_ID ASC;

#Select 4th Highest Salary – Method 2

SELECT MIN(E_ID)
FROM
(
SELECT TOP 4 E_ID
FROM EMP
ORDER BY E_ID DESC
) E; 

Description

This is one of the interesting SQL queries I came across when I was asked to find the 4th highest salary.
There are different ways to solve this problem, but using a sub query makes the logic simple and easy to understand.
In the first approach, we first get the top 4 salaries in descending order and then select the lowest value from those 4.
The second approach uses the same idea but uses `MIN()` to get the 4th highest value.
These types of SQL questions are simple to understand but are quite useful for practicing sub queries and sorting.

SQL Server: Delaying SQL Execution Using WAITFOR

WAITFOR in SQL Server
 

Example 1 – WHILE Loop


-- USE WAITFOR TO DELAY EXECUTION FOR A SPECIFIED TIME

GO

DECLARE @T INT

SET @T = 1

WAITFOR DELAY '00:00:10'

WHILE @T <= 10
BEGIN
    PRINT 'MANOJ_KUMAR'

    SET @T = @T + 1
END

GO

Example 2 – WAITFOR DELAY


GO

WAITFOR DELAY '00:00:02'

SELECT 'THIS IS DB BLOG'

GO

Description

`WAITFOR` is used in SQL Server to delay the execution of a batch, stored procedure, or transaction for a specified period of time.

The delay can be specified using `WAITFOR DELAY`, with the maximum delay being up to 24 hours.

The actual delay may be slightly longer than the specified time, depending on the activity and available resources on the SQL Server.

Every `WAITFOR` statement requires a thread while it is waiting. If too many `WAITFOR` statements are running at the same time, they can consume server resources and may contribute to thread starvation.

`WAITFOR` does not change the semantics of a query. It simply makes the execution wait for the specified period before continuing.

Conclusion

`WAITFOR` can be useful for testing scenarios where you need to introduce a delay in SQL Server. It can also be used to simulate certain blocking or timing situations during troubleshooting and testing.