January 8, 2008 at 4:54 pm
I have a table definition report file with 200 field names, field types and field sizes in Excel. What is the best way to create a new table in SQL Server 2005?
Thanks in advance,
fondac
January 9, 2008 at 8:06 am
i just took what i thought were the relevant columns, pasted them in editPlus, and then ran some find and replace macros agaisnt it...I assumed AlphaNumeric columns were varchars, and that the combination of columns Digits and Dec Pos were the Decimal Size/number of decimal places.
i would make all my decimal columns (10,2) or something to be consistant, but what's this do for you?:
CREATE TABLE SampleLayout (
YPNO DECIMAL(8,0),
YYEAR DECIMAL(4,0),
YDIST DECIMAL(3,1),
YLDUSE DECIMAL(3,0),
YEXEMP DECIMAL(2,0),
YEXCL1 VARCHAR(1),
YEXCL2 VARCHAR(1),
YTAXSV VARCHAR(4),
YBILL# DECIMAL(8,0),
YLANDV DECIMAL(9,0),
YIMPRV DECIMAL(9,0),
YPERPV DECIMAL(9,0),
YAGLNV DECIMAL(9,0),
YEXMP DECIMAL(9,0),
YNETV DECIMAL(9,0),
YNEWLV DECIMAL(9,0),
YNEWIV DECIMAL(9,0),
YNEWPV DECIMAL(9,0),
YOORNT VARCHAR(1),
YORPCT DECIMAL(3,0),
YPYGAV DECIMAL(9,0),
YANEW VARCHAR(1),
YANEWV DECIMAL(9,0),
YTICAP DECIMAL(3,1),
YTICPM VARCHAR(1),
YOCSDT DECIMAL(8,0),
YOCRDT DECIMAL(8,0),
YRASDT DECIMAL(8,0),
YRARDT VARCHAR(9),
YPPV01 DECIMAL(9,0),
YPPV02 DECIMAL(9,0),
YPPV03 DECIMAL(9,0),
YPPV04 DECIMAL(9,0),
YPPV05 DECIMAL(9,0),
YPPV06 DECIMAL(9,0),
YPPV07 DECIMAL(9,0),
YPPV08 DECIMAL(9,0),
YPPV09 DECIMAL(9,0),
YPPV10 DECIMAL(9,0),
YPPV11 DECIMAL(9,0),
YPPV12 DECIMAL(9,0),
YPPV13 DECIMAL(9,0),
YPPV14 DECIMAL(9,0),
YPPV15 DECIMAL(9,0),
YAGA01 DECIMAL(9,3),
YAGA02 DECIMAL(9,3),
YAGA03 DECIMAL(9,3),
YAGA04 DECIMAL(9,3),
YAGA05 DECIMAL(9,3),
YAGA06 DECIMAL(9,3),
YAGA07 DECIMAL(9,3),
YAGA08 DECIMAL(9,3),
YAGA09 DECIMAL(9,3),
YAGA10 DECIMAL(9,3),
YAGA11 DECIMAL(9,3),
YAGA12 DECIMAL(9,3),
YAGA13 DECIMAL(9,3),
YAGA14 DECIMAL(9,3),
YAGA15 DECIMAL(9,3),
YFSACR DECIMAL(9,3),
YNAGA1 DECIMAL(9,3),
YNAGA2 DECIMAL(9,3),
YNAGA3 DECIMAL(9,3),
YPEX01 DECIMAL(9,0),
YPEX02 DECIMAL(9,0),
YPEX03 DECIMAL(9,0),
YPEX04 DECIMAL(9,0),
YPEX05 DECIMAL(9,0),
YPEX06 DECIMAL(9,0),
YPEX07 DECIMAL(9,0),
YPEX08 DECIMAL(9,0),
YPEX09 DECIMAL(9,0),
YPEX10 DECIMAL(9,0),
YOEX01 DECIMAL(9,0),
YOEX02 DECIMAL(9,0),
YOEX03 DECIMAL(9,0),
YOEX04 DECIMAL(9,0),
YOEX05 DECIMAL(9,0),
YLIV01 DECIMAL(7,0),
YLIV02 DECIMAL(7,0),
YLIV03 DECIMAL(7,0),
YLIV04 DECIMAL(7,0),
YLIV05 DECIMAL(7,0),
YLIV06 DECIMAL(7,0),
YLIV07 DECIMAL(7,0),
YLIV08 DECIMAL(7,0),
YLIV09 DECIMAL(7,0),
YLIV10 DECIMAL(7,0),
YLIV11 DECIMAL(7,0),
YLIV12 DECIMAL(7,0),
PPNO DECIMAL(8,0),
PUPDDT DECIMAL(8,0),
POLDP# DECIMAL(8,0),
POLDDT DECIMAL(8,0),
PNXTP# DECIMAL(8,0),
PNXTDT DECIMAL(8,0),
PLOC# DECIMAL(5,0),
PLOCDR VARCHAR(2),
PLOCNM VARCHAR(32),
PLOCU# VARCHAR(9),
PTOWN VARCHAR(20),
PSUBDV VARCHAR(26),
PPNAME VARCHAR(30),
PLOT VARCHAR(5),
PBLOCK VARCHAR(3),
PMAPID VARCHAR(12),
PREMRK VARCHAR(50),
PACRES DECIMAL(9,3),
PSQFT DECIMAL(9,0),
PAGACR DECIMAL(9,3),
PWRACR DECIMAL(9,3),
P#SFDU DECIMAL(5,0),
P#MOBL DECIMAL(5,0),
P#MFIU DECIMAL(5,0),
P#SFAU DECIMAL(5,0),
P#DWEL DECIMAL(5,0),
P#NDWL DECIMAL(3,0),
P#MHHK DECIMAL(5,0),
P#WELL DECIMAL(3,0),
P#SEPT DECIMAL(3,0),
PRESSF DECIMAL(9,0),
PGARSF DECIMAL(5,0),
PGARAD VARCHAR(1),
PANAME VARCHAR(31),
PMADD1 VARCHAR(30),
PMADD2 VARCHAR(30),
PMCTST VARCHAR(30),
PZIP VARCHAR(10),
PLNAME VARCHAR(31),
PDOCDT DECIMAL(8,0),
PDEED# VARCHAR(9),
PDSFX VARCHAR(1),
PDYEAR DECIMAL(2,0),
PDBOOK DECIMAL(3,0),
PDPAGE DECIMAL(3,0),
PDCORR VARCHAR(1),
PMAP#1 VARCHAR(9),
PM1SFX VARCHAR(1),
PMAP#2 VARCHAR(9),
PM2SFX VARCHAR(1),
PMAP#3 VARCHAR(9),
PM3SFX VARCHAR(1),
PMAP#4 VARCHAR(9),
PM4SFX VARCHAR(1),
PZONE1 VARCHAR(6),
PZONE2 VARCHAR(6),
PZONE3 VARCHAR(6),
PZONE4 VARCHAR(6),
PAPRGP DECIMAL(2,0),
PFACGP DECIMAL(2,0),
PAPPYR DECIMAL(4,0),
PSPECP VARCHAR(2),
PSPECO VARCHAR(2),
PCONYR DECIMAL(4,0),
PCMPYR DECIMAL(4,0),
PDISCT VARCHAR(1),
PCLASS DECIMAL(3,2),
PUSER1 VARCHAR(10),
PUSER2 VARCHAR(10),
PUSER3 VARCHAR(10),
PUSER4 DECIMAL(9,0),
PUSER5 DECIMAL(9,0),
PUSER6 DECIMAL(9,0),
PUSER7 VARCHAR(10),
PUSER8 DECIMAL(9,0),
PUSERA VARCHAR(24),
PUSERB VARCHAR(24),
PUSERC VARCHAR(24),
PUSERD VARCHAR(24),
PUSERE VARCHAR(10),
PUSERF DECIMAL(9,0),
PUSERG DECIMAL(9,2),
PUSERH DECIMAL(9,2),
PRSPNO DECIMAL(8,0),
PBEDS VARCHAR(4),
PBATHS DECIMAL(5,2),
PSTORY DECIMAL(3,1),
PBSSQ DECIMAL(5,0),
PFBSSQ DECIMAL(5,0),
PNOTIC VARCHAR(1),
PAGMSG VARCHAR(1),
PLABEL VARCHAR(1),
PCRDAF VARCHAR(1),
PCONF VARCHAR(1),
PUPUSR VARCHAR(10)
)
Lowell
January 9, 2008 at 8:11 am
you could also do the same thing with a calculated column in Excel:
=IF(E3="N",B3 & " DECIMAL("&F3&","&G3&"),",B3 & " VARCHAR("&D3&"),")
that would return this for the fiurst column:
YPNO DECIMAL(8,0),
Lowell
January 10, 2008 at 1:33 pm
Thanks for the reply. What you did was to convert the Excel file into a text file for the Create Table command. I didn't thought about this way. I was hoping there are some existing ETL tools to use the Excel as a "format file" to create the table. I have a tab delimited file for the data to import after the table is created. Thanks a lot!
January 10, 2008 at 1:39 pm
maybe i read your attachment too quickly, but i didn't see a worksheet with the data, only the sheet with the column definitions.
if there was a worksheet with just data and a header row in it, you could import it and then tweak the resulting new table, but it's going to work either way.
also, if you wanted to just create a format file, i'd do the same thing i did before: create a formula that created teh text for the format file, and paste it for each row.
Lowell
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply