Advanced 'Insert Into' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Advanced 'Insert Into'

Author

Topic

rcamfs84
Starting Member

1 Post

Posted - 2014-10-01 : 21:00:16

Hello everyone,

This is my very first post. I'm brand new to SQL, so please bear with me. I work at a library and the data I'm trying to structure are from old 78 and 45 records. The data are both voluminous and incomplete. A short example of the problem I'm facing is as follows -

(1) 2002.400.78.00259 Bell (Haw.) LKS-199 Hilo Hattie 78rpm
(2) ' ' Bell (Haw.) LKS-200 E Mau 78rpm
(3) 2002.400.78.00260 Bell (Haw.) LKS-201 Aloha Kuu 78rpm
(4) ' ' Bell (Haw.) LKS-202 South Sea 78rpm

Songs (1) and (2) are on the same disc; songs (3) and (4) are on the same disc. The fourteen digit number is the disc accession number. I need to get my data so they look like this -

(1) 2002.400.78.00259 Bell (Haw.) LKS-199 Hilo Hattie 78rpm
(2) 2002.400.78.00259 Bell (Haw.) LKS-200 E Mau 78rpm
(3) 2002.400.78.00260 Bell (Haw.) LKS-201 Aloha Kuu 78rpm
(4) 2002.400.78.00260 Bell (Haw.) LKS-202 South Sea 78rpm

If songs are on the same disc they have the same accession numbers. My data set contains over ten thousand of these examples. I'm not even sure where to begin. Any help would be very much appreciated.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-02 : 16:31:48

It sounds like you have two entities described here: 1) Accession Number which defines the vinyl record itself and 2) Songs which are on the record. If that's true then you would need two tables to store each type and define a foreign key between the two tables to define the songs that are on each vinyl record. I'll list an incomplete approximation of the tables but you'd want to identify other attributes you'd want to track.

create table Vinyl (
   AccessionNumber char(14) not null,
   Speed           smallint not null,  -- 78, 45, 33
   Artist          varchar(50),        -- Any other attributes you want to track
   Title           varchar(50),
   Publisher       varchar(50)
   )
go
create table Tracks (
   AccessionNumber char(14) not null
      constraint FK_Tracks_Vinyl foreign key references Vinyl(AccessionNumber),
   Track           varchar(50),  -- e.g., 'LKS-199'
   Title           varchar(50),  -- Song title (e.g., 'Hilo Hattie')
   RunningTimeSec  int           -- Any other attributes...
   )

HTH

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources