Hi. I'm writing delete queries and I have the following question: if a table
has foreign keys, and I delete the fields in the tables that references the
table I want to delete, can I delete the table or must I delete the
restrictions too?
I'm having errors and I'm thinking if that's the problem.
Regards,
Diego F.You cannot drop the table that has a column referenced by another table. You
will have to remove the constraint before you can do that.
Anith|||There are basically two general ways to do this:
1) you can declare foreign keys with the ON DELETE CASCADE option - that way
when you delete the referenced (primary key table) row the referring (foreig
n
key table) rows are deleted; or
2) you delete referring rows manually (in the procedure) or use triggers to
delete referring rows when attempting to delete the referenced row.
Of course there are a few other ways to do this, however, the most important
thing to consider is the logical goal you are trying to achieve. Are you
deleteing erroneous records or are you deleting old records - the former and
the latter can also be archived, deactivated etc. Consider all options, and
use the appropriate one(s).
ML
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Question about Primary Key
I want to create a Primay Key
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>
Question about Primary Key
I want to create a Primay Key
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>|||PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce
>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>|||Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:
> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
> >--Original Message--
> >I want to create a Primay Key
> >as combination of 3 fields.
> >Prod_NO
> >Prod_Item
> >Prod_cont
> >
> >In this case there is a chance that PRod_item
> >or Prod_Cont will be blank, can I still create
> >these three as Primary Key? If not, what is the
> >alternate way to do so?
> >
> >Thank you,
> >
> >.
> >
>|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:
> > PK columns cannot be null. But you can get uniqueness by
> > creating it as a unique constraint or unique index... Bruce
> >
> >
> > >--Original Message--
> > >I want to create a Primay Key
> > >as combination of 3 fields.
> > >Prod_NO
> > >Prod_Item
> > >Prod_cont
> > >
> > >In this case there is a chance that PRod_item
> > >or Prod_Cont will be blank, can I still create
> > >these three as Primary Key? If not, what is the
> > >alternate way to do so?
> > >
> > >Thank you,
> > >
> > >.
> > >
> >
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>|||PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce
>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>|||Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:
> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
> >--Original Message--
> >I want to create a Primay Key
> >as combination of 3 fields.
> >Prod_NO
> >Prod_Item
> >Prod_cont
> >
> >In this case there is a chance that PRod_item
> >or Prod_Cont will be blank, can I still create
> >these three as Primary Key? If not, what is the
> >alternate way to do so?
> >
> >Thank you,
> >
> >.
> >
>|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:
> > PK columns cannot be null. But you can get uniqueness by
> > creating it as a unique constraint or unique index... Bruce
> >
> >
> > >--Original Message--
> > >I want to create a Primay Key
> > >as combination of 3 fields.
> > >Prod_NO
> > >Prod_Item
> > >Prod_cont
> > >
> > >In this case there is a chance that PRod_item
> > >or Prod_Cont will be blank, can I still create
> > >these three as Primary Key? If not, what is the
> > >alternate way to do so?
> > >
> > >Thank you,
> > >
> > >.
> > >
> >
Question about Primary Key
I want to create a Primay Key
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,
I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,
I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>
Monday, March 12, 2012
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!
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!
Friday, March 9, 2012
Question about by Import oracle data and field is null
Hi, I've a question about importing Oracle data and some fields are null. I get an error 'Conversion failed because the data value overflowed the specified type'. When i look in preview query result, via OLE db Source editor > Preview, this field contains '<value too big to display>'.
What do i do wrong? Can somebody help me?
Thanks in advance
Olaf
It may be helpful to have a few more details. Which version of SQL Server and Oracle are you using? And which method and tool are you using to import data?|||Hello Buck,
I work with SQL server 2005 and Oracle 7.
Olaf
|||Olaf - SQL Server has several methods to export and import data, as does Oracle. For most data cleansing issues, the simplest way to eliminate data format differences is to export the data from Oracle to a text file and then import that to SQL Server. If you're looking for something that is simpler, such as using SQL Server Integration Services (SSIS) then you'll need to check the data types on both systems to ensure that they match up. You can also "push" the data from Oracle using their export tools. Whether you pull the data from Oracle using SSIS or push it from Oracle, both servers need to have the latest client drivers for each other.|||Hi Buck,What is the problem. I have a made a SSIS, in VS2005, with an ole db source, settings to the oracle db, and an ole db destination, settings to sql 2005 db. When i look in preview
query result, via OLE db Source editor > Preview, afield
contains '<value too big to display>'. This field is a datetime oracle field and it's empty. How do i check if it's empty? And what do this field then get for an value so that i don't get problem with importing it in to SQL?
Thanks in advance
Olaf
|||
As I mentioned, you'll need the latest drivers on your SQL Server System for Oracle. Also, you can check the data you're about to import using the OPENQUERY statement in SQL Server. Look in Books Online for more information on how to use that statement.
Subscribe to:
Posts (Atom)