Create Table with Unknown Table Name?

Hi, question:

I want to create a cursor that will loop through a table and find all the distinct county names for some address records. Then, it will create a new table with each of these county names as it loops through the cursor pulling each of the records associated with these records.

My question: How do you use the INTO syntax in Microsoft Access to create a new table when you don't know the name of the table you're creating until it finds it in the database?

My code thus far: (untested, so there might be some minor syntax errors)

DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM [ALL_RECORDS]

DECLARE @.UniqueCounty

OPEN myCursor

FETCH NEXT FROM myCursor INTO @.UniqueCounty

WHILE (@.@.FETCH_STATUS=0)
BEGIN
SELECT * FROM [ALL_RECORDS] INTO @.UniqueCounty /* <-- HERE IS THE PROBLEM!!! */
FETCH NEXT FROM myCursor INTO @.UniqueCounty
ENDAside from any questions of if or why you want to do this, you will need to use dynamic sql (aka string concatenation) to accomplish your goal.

Example:

create table #ALL_RECORDS (pk int primary key, CountyName varchar(128))
insert into #ALL_RECORDS
values (1,'del_1')
insert into #ALL_RECORDS
values (2,'del_2')
insert into #ALL_RECORDS
values (3,'insertion_attack] from (select ''Gotcha'' as val ) as tab_alias; select * from master.dbo.sysxlogins -- ')
Declare @.sql nvarchar(4000)
DECLARE @.UniqueCounty sysname
DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM #ALL_RECORDS

OPEN myCursor
FETCH NEXT FROM myCursor INTO @.UniqueCounty
WHILE (@.@.FETCH_STATUS=0)
BEGIN
set @.sql = '
SELECT * INTO [' + @.UniqueCounty + ']FROM #ALL_RECORDS '
exec (@.sql)
FETCH NEXT FROM myCursor INTO @.UniqueCounty
END

/*
Note that entry 3 in #all_records demonstrates one of the perils of this method, namely that your are executing a string the exact contents of which you do not know, leaving your system vulnerable to an insertion attack.
*/|||Thanks, I will try it.

The WHY is because I need smaller source tables refreshed every night from a new gigantic database that gets refreshed every night.

At least I have a starting point, thanks!sql

Create Cursor with Dynamic SQL

I am attempting to create a store procedure that contains a cursor. I would like to Dynamically create the cursor and I am having difficulty.

Code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

CURSOR cur_gl_bucket
IS
SELECT <column name >
FROM ledger_stat
WHERE <statement>

BEGIN

Select <column name> from table;

OPEN Cursor cur_gl_bucket with retrieved column name.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

The <column name> is selected from a table based on the parameters passed into the procedure. I would then like to OPEN the cursor with the retrieved column name dynamically imbedded in the Select statment for the cursor.

I successfully use DBMS_SQL.PARSE and EXECUTE later in the procedure but have been unable to get it to work when I open the cursor. I guess the question is can this be done, and if so what is the syntax for doing this.....

Regards
Patrick McCarthy
mailto: patrick_mccarthy@.canmail.comLooks like you are on Oracle?

create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

TYPE refcur IS REF CURSOR;
cur_gl_bucket refcur;

v_column_name VARCHAR2(30);

BEGIN

Select <column name> INTO v_column_name from table;

OPEN cur_gl_bucket FOR 'SELECT '||v_column_name||' FROM ledger_stat WHERE <statement>';

Create cursor from stored procedure

I have a stored procedure which I want to view the results in a specific
sorted order, however I do not have control over the stored procedure as it
has been created with the ENCRYPTED keyword. I tried to use the INSERT
tablename (columns) EXE storedproc syntax, but I get the error about using
nested insert exec.
So my next attempt is to try the following:
DECLARE myCursor CURSOR FOR
storedprocedure
But I don't know if this is possible in any way. Could someone please give
me a suggestion?
Thanks,
MarkMark Miller wrote:
> I have a stored procedure which I want to view the results in a specific
> sorted order, however I do not have control over the stored procedure as i
t
> has been created with the ENCRYPTED keyword. I tried to use the INSERT
> tablename (columns) EXE storedproc syntax, but I get the error about using
> nested insert exec.
> So my next attempt is to try the following:
> DECLARE myCursor CURSOR FOR
> storedprocedure
> But I don't know if this is possible in any way. Could someone please give
> me a suggestion?
> Thanks,
> Mark
>
Try using OPENQUERY to execute the stored procedure:
SELECT *
FROM OPENQUERY(<<servername>>, 'EXEC storedprocedure')
ORDER BY somefield
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, Tracy.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E480DA.3090801@.realsqlguy.com...
> Mark Miller wrote:
> Try using OPENQUERY to execute the stored procedure:
> SELECT *
> FROM OPENQUERY(<<servername>>, 'EXEC storedprocedure')
> ORDER BY somefield
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Create cursor from stored procedure

I have a stored procedure which I want to view the results in a specific
sorted order, however I do not have control over the stored procedure as it
has been created with the ENCRYPTED keyword. I tried to use the INSERT
tablename (columns) EXE storedproc syntax, but I get the error about using
nested insert exec.
So my next attempt is to try the following:
DECLARE myCursor CURSOR FOR
storedprocedure
But I don't know if this is possible in any way. Could someone please give
me a suggestion?
Thanks,
MarkMark Miller wrote:
> I have a stored procedure which I want to view the results in a specific
> sorted order, however I do not have control over the stored procedure as it
> has been created with the ENCRYPTED keyword. I tried to use the INSERT
> tablename (columns) EXE storedproc syntax, but I get the error about using
> nested insert exec.
> So my next attempt is to try the following:
> DECLARE myCursor CURSOR FOR
> storedprocedure
> But I don't know if this is possible in any way. Could someone please give
> me a suggestion?
> Thanks,
> Mark
>
Try using OPENQUERY to execute the stored procedure:
SELECT *
FROM OPENQUERY(<<servername>>, 'EXEC storedprocedure')
ORDER BY somefield
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, Tracy.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E480DA.3090801@.realsqlguy.com...
> Mark Miller wrote:
>> I have a stored procedure which I want to view the results in a specific
>> sorted order, however I do not have control over the stored procedure as
>> it has been created with the ENCRYPTED keyword. I tried to use the INSERT
>> tablename (columns) EXE storedproc syntax, but I get the error about
>> using nested insert exec.
>> So my next attempt is to try the following:
>> DECLARE myCursor CURSOR FOR
>> storedprocedure
>> But I don't know if this is possible in any way. Could someone please
>> give me a suggestion?
>> Thanks,
>> Mark
> Try using OPENQUERY to execute the stored procedure:
> SELECT *
> FROM OPENQUERY(<<servername>>, 'EXEC storedprocedure')
> ORDER BY somefield
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com