question about designing big tables...

sql2k.
i'm preparing a sql table with 125 fields (columns) to import a huge
text file. i have calculated the max length needed for each of the
field.
but when i issued the create the table statement, i've got the
following message:
Warning: The table 'loc' has been created but its maximum row size
(64933) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
what can i do to work around this? suggestions are appreciated. thank
youA few options:
1 - Redesign the table. Are you sure all those columns are really attributes
of the entity? Can this table be changed to a relational design?
2- Create a 1:1 with another table. Move some of the columns to another
table that links with the first using a FK (The FK is also the PK in the
table)
3- Change your longer character columns from char/nchar/varchar/nvarchar to
text/ntext.
David Gugick
Imceda Software
www.imceda.com
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1112303316.246506.173500@.g14g2000cwa.googlegroups.com...
> sql2k.
> i'm preparing a sql table with 125 fields (columns) to import a huge
> text file. i have calculated the max length needed for each of the
> field.
> but when i issued the create the table statement, i've got the
> following message:
>
> Warning: The table 'loc' has been created but its maximum row size
> (64933) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> what can i do to work around this? suggestions are appreciated. thank
> you
>|||If in fact even one row actual contains morethan 8040 bytes, then You'll nee
d
to split the data up into two or more individual tables. How that should be
done (what type of tables, how they relate to one another, what data should
go into each one...) depends to a great degree on exactly what this data
is... It's hard to imagine a single data abstraction containing 64k+ data i
n
a single row... I suspect that this data is very far from normalized... If
that is the case, you'd be better off doing some data normalization before
you import it...
If you want some ideas, please post more about the structure of the data ...
"=== Steve L ===" wrote:

> sql2k.
> i'm preparing a sql table with 125 fields (columns) to import a huge
> text file. i have calculated the max length needed for each of the
> field.
> but when i issued the create the table statement, i've got the
> following message:
>
> Warning: The table 'loc' has been created but its maximum row size
> (64933) exceeds the maximum number of bytes per row (8060). INSERT or
> UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> what can i do to work around this? suggestions are appreciated. thank
> you
>|||ok, guys, you asked for it!
the data source is libary of congress data.
i'll post the data strucutre here (althought that's not the source data
is constructed, the source data is all in text format so split the data
will be difficult)
i'm aslo posting a few records here (i parsed it to record number,
column name, and data in the column. can someone also tell me how to
transpose or crosss tab a query to populate the table structure? i know
this is not a easy question but any suggestions would be appreciated.
the table strucutre is like
CREATE TABLE [loc] (
[Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Agency responsible for reproduction] [varchar] (180) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Arranged statement for music] [varchar] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bibliography note] [varchar] (1361) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Biography] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Biography/Autobiography/Memoir] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Born] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bulk dates] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Canceled/invalid ISBN] [varchar] (92) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Century] [varchar] (106) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Character name] [varchar] (281) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Conference publication] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Corporate or govt. name] [varchar] (326) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Creation/production credits note] [varchar] (1032) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of a work] [varchar] (56) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Date of distribution/release/issue and production/recordingsession
when different] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date of manufacture] [varchar] (80) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of meeting or treaty signing] [varchar] (95) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of reproduction] [varchar] (322) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of treaty signing] [varchar] (39) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date of work] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Dates associated with a name] [varchar] (14) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Dates of publication and/or sequential designation of issues
reproduced] [varchar] (407) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dates unknown] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Detailed date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Dissertation note] [varchar] (450) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Edition statement] [varchar] (782) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Expansion of summary note] [varchar] (1054) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Festschrift] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Form] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Form of item] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Form subheading] [varchar] (244) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Formatted contents] [varchar] (7826) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Full LCCN] [varchar] (41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fuller form of name] [varchar] (55) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[General note] [varchar] (3781) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Genre] [varchar] (1198) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Geographic area] [varchar] (68) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Government publication] [varchar] (46) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Illustrations] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Inclusive dates] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Inclusive dates of collection] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Institution to which field applies] [varchar] (137) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[International standard serial number] [varchar] (167) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ISBN] [varchar] (152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ISSN] [varchar] (146) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Key for music] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Language] [varchar] (58) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of accompanying material other than librettos]
[varchar] (62) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of summary or abstract/overprinted title or subtitle]
[varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Language code of sung or spoken text] [varchar] (134) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of a work] [varchar] (135) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of librettos] [varchar] (134) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Language of table of contents] [varchar] (51) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LCCN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Library of Congress call number] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Literary form] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Location of meeting] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Location of unit] [varchar] (97) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Manufacturer] [varchar] (176) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Materials specified] [varchar] (65) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Medium] [varchar] (79) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Medium of performance for music] [varchar] (77) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Miscellaneous information] [varchar] (1488) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Multiple dates] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Music. Appl. au.] [varchar] (207) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of part/section of a work] [varchar] (619) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of part/section of work] [varchar] (259) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Nature of record] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[No dates given] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Non-geographic setting] [varchar] (1514) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Note about reproduction] [varchar] (860) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section of a work] [varchar] (437) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section of work] [varchar] (69) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of part/section/meeting] [varchar] (103) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Number of references] [varchar] (17) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Numeration] [varchar] (151) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Original language] [varchar] (105) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Participate or performer note] [varchar] (1391) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Physical] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Physical description of reproduction] [varchar] (143) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Place of manufacture] [varchar] (116) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Place of reproduction] [varchar] (117) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Plate or publisher's number for music] [varchar] (48) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Primary Author] [varchar] (1215) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Pub. and (c) dates] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication date] [varchar] (189) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication date and copyright date] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Published in] [varchar] (324) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Publisher] [varchar] (508) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Publisher number] [varchar] (236) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Questionable date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Range of years of bulk of collection] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Relator code] [varchar] (54) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Relator term] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Remainder of edition statement] [varchar] (1252) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Reprint & original date] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial item ceased publication] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial status unknown] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Series] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Series statement] [varchar] (831) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Series statement of reproduction] [varchar] (1062) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [varchar] (340) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Statement of responsibility] [varchar] (721) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Sub-genre] [varchar] (87) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subject] [varchar] (756) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subordinate unit] [varchar] (364) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Summary note] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Target audience] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Title] [varchar] (1827) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title of a work] [varchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Titles and other words associated with a name] [varchar] (36) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Topic] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type of reproduction] [varchar] (339) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Uncontrolled related/analytical title] [varchar] (603) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Uniform title] [varchar] (441) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Version] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume number/sequential designation] [varchar] (404) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[With index] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Year] [varchar] (358) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
and the sample data from the data source is in another table in the
format: (record number, column name, and data in the column)
1 Primary Author Zapf, Hermann
1 Title ABC-XYZapf :fifty years in alphabet design : professional and
personal contributions selected for Hermann Zapf
1 Author edited by John Dreyfus and Knut Erichson
1 Author Zapf, Hermann, Dreyfus, John, Erichson, Knut,
1 LCCN 90229151
1 Full LCCN a90229151
1 Subject Zapf, Hermann.
1 Subject Type and type-founding -- History -- 20th century.
1 Subject Calligraphy -- History -- 20th century.
1 Subject Alphabets.
1 Year 1989
1 Published in England
1 Illustrations Yes
1 Publisher Wynkyn de Worde Society ;Bund Deutscher Buchkeunstler
;Distribution by Kelly/Winterton Press,
1 Physical 251 p. :ill. (some col.), facsims. (some col.) ;28 cm.
1 Genre qr02 input 12-19-90; sent to SpecMat 12-19-90; vj05 12-24-90;
SCD notified 1/15/91; fd11 01-22-91; fp04 03-11-91
1 ISBN 3922715354 (Bund Deutscher Buchkeunstler)
1 Language of a work English, German
1 Published in London :
1 Published in Offenbach :
1 Published in New York :
1 Publication date c1989
1 Place of manufacture (Frankfurt :
1 Manufacturer Oehms Druck GmbH)
1 General note Articles in English and German; pref. and notes in
English.
1 General note Source: Purchase, July 27, 1990.
1 Institution to which field applies DLC
1 Uncontrolled related/analytical title ABCXYZapf.
1 Award rarebk/rbc
2 Primary Author Finley, Eric J
2 Title The old U.S. 80 highway traveler's guide (Phoenix-San Diego)
2 Author by Eric J. Finley ; photos and maps by Eric J. Finley ;
illustrations by Kevin Finley ; foreword by Jerry Jacka & Lois Essary
Jacka
2 LCCN 97222356
2 Full LCCN a97222356
2 Subject Arizona -- Guidebooks.
2 Subject California -- Guidebooks.
2 Subject Automobile travel -- Arizona -- Guidebooks.
2 Subject Automobile travel -- California -- Guidebooks.
2 Subject Interstate 80 -- Guidebooks.
2 Year 1997
2 Published in Arizona
2 Illustrations Maps
2 With index yes
2 Publisher Narrow Road Communications ;Inkling Communications Design,
2 Physical xx, 130 p. :ill., maps ;32 cm.
2 Genre jk27/jk15 (desc); (F809.3); lk03 to sl 02-04-98; lk18 2-23-98
2 ISBN 0965235807
2 Geographic area Arizona
2 Geographic area California
2 Published in Phoenix, AZ :
2 Published in Mesa, AZ :
2 Publication date c1997.
2 Bibliography note Includes bibliographical references (p. 115-117)
and index.
2 Character name **LC HAS REQ'D # OF SHELF COPIES**
3 Primary Author Mundy, Talbot
3 Title Old Ugly-face,
3 Author by Talbot Mundy
3 LCCN 40004503
3 Full LCCN a40004503
3 Year 1940
3 Published in New York (State)
3 Publisher D. Appleton-Century Company, incorporated,
3 Physical 5 p. l., 3-544 p.21 cm.
3 Born 1879-1940.
3 Published in New York,
3 Publication date 1940.
3 General note Illustrated lining-papers.|||Wow, and the columns are even in alphabetical order. Nice!
I agree with the others. Do something to normalize this data a little bit
better before trying to deal with it in the database.
Is every single row going to have an "Arranged statement for music"? Is
"Author" really ever going to require 5174 bytes? Is there always going to
be 1.3 KB worth of "Bibliography note"? I didn't even get through B and I'm
already having nightmares.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1112305705.741957.216250@.l41g2000cwc.googlegroups.com...
> ok, guys, you asked for it!
> the data source is libary of congress data.
> i'll post the data strucutre here (althought that's not the source data
> is constructed, the source data is all in text format so split the data
> will be difficult)
> i'm aslo posting a few records here (i parsed it to record number,
> column name, and data in the column. can someone also tell me how to
> transpose or crosss tab a query to populate the table structure? i know
> this is not a easy question but any suggestions would be appreciated.
> the table strucutre is like
> CREATE TABLE [loc] (
> [Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Agency responsible for reproduction] [varchar] (180) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arranged statement for music] [varchar] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bibliography note] [varchar] (1361) COLLATE|||On Thu, 31 Mar 2005 16:54:21 -0500, Aaron [SQL Server MVP] wrote:

>Wow, and the columns are even in alphabetical order. Nice!
>I agree with the others. Do something to normalize this data a little bit
>better before trying to deal with it in the database.
>Is every single row going to have an "Arranged statement for music"? Is
>"Author" really ever going to require 5174 bytes? Is there always going to
>be 1.3 KB worth of "Bibliography note"? I didn't even get through B and I'
m
>already having nightmares.
Hi Aaron,
Be glad you stopped reading at B - I skimmed over the whole list, and I
am now haunted by the vision of varchar(322) dates, varchar(152) ISBN,
varchar(17) numbers, and (my favorite) varchar(358) years.
(shudder)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Whew!
Just as I suspected... Steve, what you should seriously consider doing
is investing some time in normalizing the data structure... For Example, You
don't need Autor Info in every record, Create an Authors table,
(coincidently SQL Server Ships with a "Pubs" Database that might give you
some ideas) and only put the key to the Author record in the main loc table
,
voila ! That does away with 8-10k
etc. etc.
Until you do this you will not easily resolve this issue...
"Hugo Kornelis" wrote:

> On Thu, 31 Mar 2005 16:54:21 -0500, Aaron [SQL Server MVP] wrote:
>
> Hi Aaron,
> Be glad you stopped reading at B - I skimmed over the whole list, and I
> am now haunted by the vision of varchar(322) dates, varchar(152) ISBN,
> varchar(17) numbers, and (my favorite) varchar(358) years.
> (shudder)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||That is a work of art!!!!
I'd hate to be a developer writing reports against this beast.
SELECT
[Date of distribution/release/issue and
production/recordingsession when different]
,[Dates of publication and/or sequential designation of issues
reproduced]
,[Language code of accompanying material other than librettos]
,[Language code of summary or abstract/overprinted title or subtitle]
,[Series statement of reproduction]
,[Titles and other words associated with a name]
FROM
loc
WHERE
ISDATE(SUBSTRING([Dates of publication and/or sequential
designation of issues reproduced], 1, 8...
That's alot of typing!!!
You need to normalize that before anyone gets hurt!
Simon Worth
=== Steve L === wrote:
> ok, guys, you asked for it!
> the data source is libary of congress data.
> i'll post the data strucutre here (althought that's not the source data
> is constructed, the source data is all in text format so split the data
> will be difficult)
> i'm aslo posting a few records here (i parsed it to record number,
> column name, and data in the column. can someone also tell me how to
> transpose or crosss tab a query to populate the table structure? i know
> this is not a easy question but any suggestions would be appreciated.
> the table strucutre is like
> CREATE TABLE [loc] (
> [Affiliation] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Agency responsible for reproduction] [varchar] (180) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arranged statement for music] [varchar] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (5174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author notes] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Award] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bibliography note] [varchar] (1361) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Biography] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Biography/Autobiography/Memoir] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Born] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Bulk dates] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Canceled/invalid ISBN] [varchar] (92) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Century] [varchar] (106) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Character name] [varchar] (281) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Conference publication] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Corporate or govt. name] [varchar] (326) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Creation/production credits note] [varchar] (1032) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of a work] [varchar] (56) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Date of distribution/release/issue and production/recordingsession
> when different] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Date of manufacture] [varchar] (80) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of meeting or treaty signing] [varchar] (95) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of reproduction] [varchar] (322) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of treaty signing] [varchar] (39) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date of work] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Dates associated with a name] [varchar] (14) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Dates of publication and/or sequential designation of issues
> reproduced] [varchar] (407) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Dates unknown] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Detailed date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Dissertation note] [varchar] (450) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Edition statement] [varchar] (782) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Expansion of summary note] [varchar] (1054) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Festschrift] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Form] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Form of item] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Form subheading] [varchar] (244) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Formatted contents] [varchar] (7826) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Full LCCN] [varchar] (41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fuller form of name] [varchar] (55) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [General note] [varchar] (3781) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Genre] [varchar] (1198) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Geographic area] [varchar] (68) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Government publication] [varchar] (46) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Illustrations] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Inclusive dates] [varchar] (104) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Inclusive dates of collection] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Institution to which field applies] [varchar] (137) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [International standard serial number] [varchar] (167) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISBN] [varchar] (152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISSN] [varchar] (146) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Key for music] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Language] [varchar] (58) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of accompanying material other than librettos]
> [varchar] (62) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of summary or abstract/overprinted title or subtitle]
> [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language code of sung or spoken text] [varchar] (134) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of a work] [varchar] (135) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of librettos] [varchar] (134) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Language of table of contents] [varchar] (51) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LCCN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Library of Congress call number] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Literary form] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Location of meeting] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Location of unit] [varchar] (97) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Manufacturer] [varchar] (176) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Materials specified] [varchar] (65) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Medium] [varchar] (79) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Medium of performance for music] [varchar] (77) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Miscellaneous information] [varchar] (1488) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Multiple dates] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Music. Appl. au.] [varchar] (207) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Name of part/section of a work] [varchar] (619) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Name of part/section of work] [varchar] (259) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Nature of record] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [No dates given] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Non-geographic setting] [varchar] (1514) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Note about reproduction] [varchar] (860) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section of a work] [varchar] (437) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section of work] [varchar] (69) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of part/section/meeting] [varchar] (103) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Number of references] [varchar] (17) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Numeration] [varchar] (151) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Original language] [varchar] (105) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Participate or performer note] [varchar] (1391) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Physical] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Physical description of reproduction] [varchar] (143) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Place of manufacture] [varchar] (116) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Place of reproduction] [varchar] (117) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Plate or publisher's number for music] [varchar] (48) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Primary Author] [varchar] (1215) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Pub. and (c) dates] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Publication date] [varchar] (189) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Publication date and copyright date] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Published in] [varchar] (324) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Publisher] [varchar] (508) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Publisher number] [varchar] (236) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Questionable date] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Range of years of bulk of collection] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Relator code] [varchar] (54) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Relator term] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Remainder of edition statement] [varchar] (1252) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Reprint & original date] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Serial item ceased publication] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Serial status unknown] [varchar] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series statement] [varchar] (831) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Series statement of reproduction] [varchar] (1062) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Source] [varchar] (340) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Statement of responsibility] [varchar] (721) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sub-genre] [varchar] (87) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Subject] [varchar] (756) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Subordinate unit] [varchar] (364) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Summary note] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Target audience] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Title] [varchar] (1827) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title of a work] [varchar] (440) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Titles and other words associated with a name] [varchar] (36) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Topic] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type of reproduction] [varchar] (339) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Uncontrolled related/analytical title] [varchar] (603) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Uniform title] [varchar] (441) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Version] [varchar] (78) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Volume] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Volume number/sequential designation] [varchar] (404) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [With index] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Year] [varchar] (358) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> and the sample data from the data source is in another table in the
> format: (record number, column name, and data in the column)
> 1 Primary Author Zapf, Hermann
> 1 Title ABC-XYZapf :fifty years in alphabet design : professional and
> personal contributions selected for Hermann Zapf
> 1 Author edited by John Dreyfus and Knut Erichson
> 1 Author Zapf, Hermann, Dreyfus, John, Erichson, Knut,
> 1 LCCN 90229151
> 1 Full LCCN a90229151
> 1 Subject Zapf, Hermann.
> 1 Subject Type and type-founding -- History -- 20th century.
> 1 Subject Calligraphy -- History -- 20th century.
> 1 Subject Alphabets.
> 1 Year 1989
> 1 Published in England
> 1 Illustrations Yes
> 1 Publisher Wynkyn de Worde Society ;Bund Deutscher Buchkeunstler
> ;Distribution by Kelly/Winterton Press,
> 1 Physical 251 p. :ill. (some col.), facsims. (some col.) ;28 cm.
> 1 Genre qr02 input 12-19-90; sent to SpecMat 12-19-90; vj05 12-24-90;
> SCD notified 1/15/91; fd11 01-22-91; fp04 03-11-91
> 1 ISBN 3922715354 (Bund Deutscher Buchkeunstler)
> 1 Language of a work English, German
> 1 Published in London :
> 1 Published in Offenbach :
> 1 Published in New York :
> 1 Publication date c1989
> 1 Place of manufacture (Frankfurt :
> 1 Manufacturer Oehms Druck GmbH)
> 1 General note Articles in English and German; pref. and notes in
> English.
> 1 General note Source: Purchase, July 27, 1990.
> 1 Institution to which field applies DLC
> 1 Uncontrolled related/analytical title ABCXYZapf.
> 1 Award rarebk/rbc
> 2 Primary Author Finley, Eric J
> 2 Title The old U.S. 80 highway traveler's guide (Phoenix-San Diego)
> 2 Author by Eric J. Finley ; photos and maps by Eric J. Finley ;
> illustrations by Kevin Finley ; foreword by Jerry Jacka & Lois Essary
> Jacka
> 2 LCCN 97222356
> 2 Full LCCN a97222356
> 2 Subject Arizona -- Guidebooks.
> 2 Subject California -- Guidebooks.
> 2 Subject Automobile travel -- Arizona -- Guidebooks.
> 2 Subject Automobile travel -- California -- Guidebooks.
> 2 Subject Interstate 80 -- Guidebooks.
> 2 Year 1997
> 2 Published in Arizona
> 2 Illustrations Maps
> 2 With index yes
> 2 Publisher Narrow Road Communications ;Inkling Communications Design,
> 2 Physical xx, 130 p. :ill., maps ;32 cm.
> 2 Genre jk27/jk15 (desc); (F809.3); lk03 to sl 02-04-98; lk18 2-23-98
> 2 ISBN 0965235807
> 2 Geographic area Arizona
> 2 Geographic area California
> 2 Published in Phoenix, AZ :
> 2 Published in Mesa, AZ :
> 2 Publication date c1997.
> 2 Bibliography note Includes bibliographical references (p. 115-117)
> and index.
> 2 Character name **LC HAS REQ'D # OF SHELF COPIES**
> 3 Primary Author Mundy, Talbot
> 3 Title Old Ugly-face,
> 3 Author by Talbot Mundy
> 3 LCCN 40004503
> 3 Full LCCN a40004503
> 3 Year 1940
> 3 Published in New York (State)
> 3 Publisher D. Appleton-Century Company, incorporated,
> 3 Physical 5 p. l., 3-544 p.21 cm.
> 3 Born 1879-1940.
> 3 Published in New York,
> 3 Publication date 1940.
> 3 General note Illustrated lining-papers.
>|||hey, thank you guys. for all the good suggestions!