Create new table using information from another table

[Rexolio]

I have an archaic table with the following format:

tblCATEGORIES
NameID, Category1, Category2, Category3, Category4, so on

Each record contains an ID (NameID) from another table and specifies whether that user/record has selected any of those categories. Each category is represented by a number. So, say Name ID # 450 chose categories 1 and 3, the entry would look like:

450, 1, , 3,,

I want to create a NEW table with only 2 fields, NameID and CategoryID, and then take the info from the existing table and transfer it so that user 450 would have 2 entries:

450, 1
450, 3

But I don't know how to transfer/interact with the data like that without building an ASP page to do it and that really hits the server hard since there are about 300,000 records currently.

Any suggestions?


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[SQLSister]

Create your table withthe create table command

Run inserts like so
Insert into table2 (NameID, Category)
Select NAmeID, Category1 from table1

Insert into table2 (NameID, Category)
Select NAmeID, Category2 from table1
etc through athe category fields.

 

[Rexolio]

Thanks - I managed to start playing with DTS and exporting information using the above query you sampled. The only problem, and its not just a HUGE ordeal or anything, but a little inconvenience, is that let's say on Category1, not all records have a 1 in Category1 or a 2 in Category2, etc. So, if the record is null in Category1 but has a 2 in Category, the transfer inserts 2 records for the Name ID , one with a null in the new table under Category, and a second with a 2. I tried adding "WHERE Category1 <> ''" and "WHERE Category2 <> ''" to the query, but that just gives me an error.


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Rexolio]

I spoke too soon - I keep getting an Invalid Pointer error


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Rexolio]

Or I get "Line 3: Incorrect syntax near ')'" even though DTS parses it without any problem.

OR I get "Context: Error calling GetColumnInfo. Your provider does not support all of the interfaces/methods required by DTS"

I'm confused - I can't find where it changes, but I get various errors. And my provider swears I have access to everything.


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Rexolio]

Sorry... just consider this my "vent" forum as well... I think I have it figured out. Its amazing how easy things work when you let QueryBuilder do the work!!!

Geesh!




rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes