Can someone help me translate the following TSQL code into PL/SQL? I want to create temporary tables and populate them with some sample data to demonstrate a SQL query. What is the way to do this with minimal typing?
Does PLSQL have a compact way of populating a table like the INSERT/SELECT/UNION statement in TSQ? I thought I saw such a feature on some web page and I cannot find it. I've also been experimenting with the CREATE GLOBAL TEMPORARY TABLE and the DECLARE LOCAL TEMPORARY TABLE commands and I think I really want the latter. I cannot make the DECLEAR LOCAL TEMPORARY TABLE command work in Oracle 11 c, however. What is the scope of a temporary table in PLSQL? Is it automagically deleted (dropped) when my script exits?
Thanks
siegfried
DECLARE @temptable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)
INSERT INTO @temptable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700
SELECT * FROM @temptable ;