GrassHopper (3/1/2015)
I have a list in Excel that has a list of about 30 tables with all column names and types. How can I programatically create the tables using this list with the datatypes listed in the excel file ? I thought of importing the list into a table and using a cursor to loop thru the list and build the Create table command, but I've never used cursors before.Column_Heading is just a label...use column_name as the column name to be used. Also, the Char data_type could be Varchar.
This is a sample of the list:
TABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_HEADINGDATA_TYPELENGTHNUMERIC_SCALE
AAHHSTHPAAHCMPN1Company number CHAR3
AAHHSTHPAAHDIVN2Division number CHAR3
AAHHSTHPAAHDPTN3Department number CHAR3
AAHHSTHPAAHVNDN4Vendor number CHAR10
AAHHSTHPAAHPINB5Journal inv/ref numbCHAR10
AAHHSTHPAAHLNAH6History line number NUMERIC50
AAHHSTHPAAHIAMG7Invoice amount-grossDECIMAL152
AAHHSTHPAAHIAMD8Invoice discount amoDECIMAL152
AAHHSTHPAAHIAMZ9Invoice amount-no dsDECIMAL152
AAIHSTDPAAICMPN1Company number CHAR3
AAIHSTDPAAIDIVN2Division number CHAR3
AAIHSTDPAAIDPTN3Department number CHAR3
AAIHSTDPAAIVNDN4Vendor number CHAR10
AAIHSTDPAAIPINB5Journal inv/ref numbCHAR10
AAIHSTDPAAILNAH6History line number NUMERIC50
AAIHSTDPAAILNAP7Payable line number NUMERIC50
You first need to identify the UNIQUE columns for each table for a PK or, at the very least, what the keys will be used for a clustered index on each table. A lot of people will argue against it but in 99.9% of the cases, at least programmers will benefit by correctly identifying a PK and, whether it becomes the PK or not, 99.9% of all tables should have a Clustered Index. Without those things, you don't really have tables.
Then, look at the datatypes. Do you really need the "number" columns to be character based, do you really need the 9 bytes that Decimal(15,2) will take or can you live with the 5 bytes of Decimal(9,2), do you really need the 5 bytes of NUMERIC(5,0) or can you live with the 2 bytes of SMALLINT and certainly the 4 bytes of just INT.
I also think that the table and column names are horrific but I'm thing that there's not much we can do there.
I also recommend that you read and heed the article at the first link under "Helpful Links" in my signature line below if you want any form of coded help.
--Jeff Moden
Change is inevitable... Change for the better is not.