One Record from many tables

[Azzuron]

Hello folks. I have a question regarding a database i am working with. I am no expert on the MSSQL server nor any other really i guess...but i do know some SQL, and i have been trying to find a solution to my problem on MSSQL to have little luck.

Here is my situation:

We have flat files of data that are downloaded once per day. These files are imported Via BCP into some holding tables. We then have a "Translation" database that holds all the core information our "Cart" database requires. I am in the process of writing a series of SQL strings that will load data from the various imported tables into the translation database.

The specific part that is giving me a problem, is the one set of flat files is organized by Manufacture. 3com for example... Each of these tables has a field in it that specifies the manufacture also.

We have in the translation database a list of categories, my goal is to take each of the manufactures from the tables and add them as a category.

So, as far as i can tell what i need to do is this:

Get a List of all tables in <parts> database
Get 1 record (any record) form each table "SELECT TOP 1 [manufacture] from <parts>"
Insert the value of Manufacture to the categories database

Thats about it. This will probably require some kind of loop, i think it was a counter i saw? My main issue has been getting a list of tables (only the product tables) from the database. It only has part tables that i have made... i don't know if it has any system tables build into the database though.

Hope someone can give me some ideas here! Thanks guys & girls.


--Dave

[willakawill]

Hello folks. I have a question regarding a database i am working with. I am no expert on the MSSQL server nor any other really i guess...but i do know some SQL, and i have been trying to find a solution to my problem on MSSQL to have little luck.

Here is my situation:

We have flat files of data that are downloaded once per day. These files are imported Via BCP into some holding tables. We then have a "Translation" database that holds all the core information our "Cart" database requires. I am in the process of writing a series of SQL strings that will load data from the various imported tables into the translation database.

The specific part that is giving me a problem, is the one set of flat files is organized by Manufacture. 3com for example... Each of these tables has a field in it that specifies the manufacture also.

We have in the translation database a list of categories, my goal is to take each of the manufactures from the tables and add them as a category.

So, as far as i can tell what i need to do is this:

Get a List of all tables in <parts> database
Get 1 record (any record) form each table "SELECT TOP 1 [manufacture] from <parts>"
Insert the value of Manufacture to the categories database

Thats about it. This will probably require some kind of loop, i think it was a counter i saw? My main issue has been getting a list of tables (only the product tables) from the database. It only has part tables that i have made... i don't know if it has any system tables build into the database though.

Hope someone can give me some ideas here! Thanks guys & girls.


--Dave

Hi. The way you have described it sounds like this will do the job

INSERT INTO categories
SELECT DISTINCT Manufacturer
FROM Parts