March 1, 2015 at 8:00 pm
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
March 1, 2015 at 9:32 pm
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.
March 1, 2015 at 10:35 pm
I tried this but it only gives me the first column.... what am I doing wrong?
-- START HERE
DECLARE @SQL NVARCHAR(MAX)
DECLARE @TBLNAME VARCHAR(100)
DECLARE TBLS CURSOR FOR
SELECT DISTINCT [TABLE_NAME]
FROM [UNFI_Canada].[dbo].[Table_Schemas]
OPEN TBLS
FETCH NEXT
FROM TBLS
INTO @TBLNAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TBLNAME
--DECLARE @SQL VARCHAR(MAX)
--DECLARE @TBLNAME VARCHAR(100)
--SET @TBLNAME = 'HHKORDAP'
SELECT @SQL = Column_Name + ' ' + DATA_TYPE + ' (' + [LENGTH] +
CASE LEN(NUMERIC_SCALE)
WHEN 0
THEN ''
ELSE ',' + NUMERIC_SCALE
END + ') ,'
FROM [UNFI_Canada].[dbo].[Table_Schemas]
WHERE TABLE_NAME = @TBLNAME
SET @SQL = 'CREATE TABLE DBO.' + @TBLNAME + '(' + @SQL + ')'
PRINT @SQL
--EXEC (@SQL)
FETCH NEXT FROM TBLS
END
CLOSE TBLS
DEALLOCATE TBLS
March 2, 2015 at 7:44 am
GrassHopper (3/1/2015)
I tried this but it only gives me the first column.... what am I doing wrong?
Since your code has no formatting it is hard to tell for sure. The biggest thing you are doing wrong however is you didn't read the article Jeff recommended about how to post. We can't see your screen. The only information we have is what you posted, which isn't much at this point. Help us help you by giving us some details.
The logistics of what you are doing isn't that bad. I have to agree with Jeff though that the datatypes and names are pretty horrific.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply