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 dynamic reports

Hi,

I receive an xml object that represent just about any kind of a table containing unknown amount of text fields, and need to create a Table report out of it, the header fields are not pre-determined.

Is this possible with Crystal reports and in particular in the .Net free addition of Crystal reports ?

if so - HOW ? please explain as articulately as possible

Thanks
. .
|
~

NimCo.You can try using CDO (Crystal Data Objects) to pass info to Crystal Reports. Basically, you populate an array and pass the array to the report.

Look here for an example of CDO in VB 6, maybe you can adapt it to your needs...

http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo