Tag Archives: Oracle

SQL Server: Linked server select query into a new table

It’s always worth remembering SELECT INTO a new table or a temporary table, here’s and example:

SELECT [COL1], [COL2]
INTO [DB name].[Schema name].[Table name]
FROM [Source DB name].[Source Schema name].[Source Table name]

This will create the new table with the columns matching the name and types of the selected columns.
Alternatively you could select into a temporary table like so:

SELECT [COL1], [COL2]
INTO #temporarytable
FROM [Source DB name].[Source Schema name].[Source Table name]

For performance reasons you may want to copy the contents of a linked server into new or temporary tables in SQL Server, then run complex scripts and SQL against your SQL instance. Here’s an example of how I would do this, I am using OPENQUERY because that takes care of type inconsistencies for me:

SELECT [COL1], [COL2]
INTO [DB name].[Schema name].[Table name]
FROM OPENQUERY([Linked Server], N'SELECT * FROM SourceSchema.SourceTable')

Of course here your linked server could be anything, but I’ve used this for Oracle most recently and it was so much better than trying to export the data from Oracle and import it into SQL Server.

Advertisements

Oracle: Trim, replace, substring and indexof #oracle

Further to my post about SQL Server, here’s the differences I need to consider when scripting the same thing in Oracle:
REVERSE AND REPLACE are the same 🙂
LEN => LENGTH
LEFT => SUBSTR
CHARINDEX => INSTR
(String concatenation) + => ||
PATINDEX => REGEXP_INSTR (which is more powerful out of the box, of course you can do regex with SQL Server, but not out of the box)

SQL Server / Oracle: DISTINCT or GROUP BY

Firstly, sometimes sticking in a DISTINCT is quick and easy, but so easily missed when reading the SQL and also conversely the group by can be pretty long if there are a lot of columns selected.

Firstly a basic example:
SQL Server

SELECT
a
FROM
(SELECT 1 AS a
UNION ALL SELECT 1 AS a
UNION ALL SELECT 2 AS a) b
GROUP BY a

SELECT
DISTINCT a
FROM
(SELECT 1 AS a
UNION ALL SELECT 1 AS a
UNION ALL SELECT 2 AS a) b

Oracle

SELECT
a
FROM
(SELECT 1 AS a FROM DUAL
UNION ALL SELECT 1 AS a FROM DUAL
UNION ALL SELECT 2 AS a FROM DUAL) b
GROUP BY a;

SELECT
DISTINCT a
FROM
(SELECT 1 AS a FROM DUAL
UNION ALL SELECT 1 AS a FROM DUAL
UNION ALL SELECT 2 AS a FROM DUAL) b;

They both return the same result, simple, now imagine a lot more columns in the select and make your choice of DISTINCT or GROUP BY carefully.

Is COUNT(DISTINCT a) the same as DISTINCT COUNT(a)?
No, the former you select distinct then count them, the later you count then select the distinct counts, so results are 2 and 3.
Also you can’t swap DISTINCT for a GROUP BY in this case, because the GROUP BY would return you two counts, one for each grouped value, however using a nested query you could select the count from the grouped values, like so:

SQL Server

SELECT COUNT(*)
FROM
(
	SELECT
	a
	FROM
	(SELECT 1 AS a
	UNION ALL SELECT 1 AS a
	UNION ALL SELECT 2 AS a) b
	GROUP BY a
) c

Oracle

SELECT COUNT(*)
FROM
(
	SELECT
	a
	FROM
	(SELECT 1 AS a FROM DUAL
	UNION ALL SELECT 1 AS a FROM DUAL
	UNION ALL SELECT 2 AS a FROM DUAL) b
	GROUP BY a
) c;

I always try to remind people that DISTINCT works on what is selected, don’t just go sticking it wherever you feel like as I won’t see it, put it right after the SELECT as a means of helping me see it.

There is also the group by pitfall of grouping on something that you aren’t selecting. Let’s say you originally select two columns grouping on both so you get distinct values, great, then you decide, actually I am only interested in the first column so the second is removed from the select, unless it is also removed from the group by the results will not be the same as distinct! This sort of thing normally leads to the group by being dropped and replaced by distinct.

I hope this helps.

Export SQL Server data and import into Oracle

It’s worth making a note of characterset issues as we come across them.

Let’s say I have some data in SQL Server:

SELECT 'Jörg' AS Name
UNION SELECT 'Jones' AS Name

And I export it using the “Save Results As…” option and save as test.csv.

Great, now we have this unicode file and we need to import it, for the unicode character to be supported by Oracle SQL Developer there is a setting you may need to change.
With the default setting of Cp1252:

when I open the test.csv I see:

Those with a keen eye will spot the Byte Order Mark (BOM), but ouch, look at what has happened to Jörg.

So let’s change the Encoding setting to UTF-8:

Then reopen and voila:

The Oracle SQL Developer Data Import Wizard can trick you too, if Encoding setting is set to default then it won’t be the UTF-8 we’ve set, you will need to manually change this to UTF-8 each time you do an import:

Oracle: Commit; just in case you forget

I’ve been writing a few Oracle scripts recently and it dawned on me that I should really stick a COMMIT; at the end of every script rather than forgetting and not actually applying the update. I know it is a minor thing, but when switching between SQL Server and Oracle it is often forgot.

Oracle: Ampersand (&) substitution in SQL Developer

Try this:
SELECT 'HELLO & GOODBYE' FROM DUAL;
and you will get a popup:

Ampersand is used to denote a substitution variable, great, but in this case I just want the & character in my string. A workaround is to put the ampersand at the end of a string, so the following will work:
SELECT 'HELLO &' || ' GOODBYE' FROM DUAL;
Where || is the Oracle string concatenation function.

Oracle: ROWNUM doesn’t equals

I often forget ROWNUM and the equals quirk, also not forgetting the ORDER BY/WHERE and ROWNUM combination issue too.
-- Works - result is two rows (bye, hello)
SELECT * FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
) WHERE ROWNUM < 3;

-- Doesn't
SELECT * FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
) WHERE ROWNUM = 2;

This is apparently because (I quote) “the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row” from Oracle ROWNUM Documentation.
So it is calculated on the select, this means you have to be careful with ORDER BYs and WHEREs (etc) and explains why it doesn’t work as we hoped.

-- So this works; returns one row (hello)
SELECT "Greeting" FROM
(
SELECT "Greeting", ROWNUM AS "N" FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
)
) WHERE N = 2;

There are other ways, but I am only focusing on ROWNUM in this post.