Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    2

    Question Unanswered: 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
    END

  2. #2
    Join Date
    May 2003
    Posts
    4

    Dynamic SQL

    Aside 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.
    */

  3. #3
    Join Date
    May 2003
    Posts
    2

    Re: Dynamic SQL

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •