Insert in Musically JOINED Tables

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

SQL Server 2000 Forums

Transact-SQL (2000)

Insert in Musically JOINED Tables

Author

Topic

Eulibrius7
Starting Member

6 Posts

Posted - 2004-06-08 : 22:38:38

What is the most efficient method (to a SQL newbie) to create a Stored Procedure that will Insert/Update in the following joined table setup. For CRUD have an ASPX based DataGrid pulling a from a view:


CREATE VIEW dbo.vwMusic
AS
SELECT     TOP 100 PERCENT dbo.tblMusic_Song.ID, dbo.tblMusic_Song.Song, dbo.tblMusic_Song.ArtistID, dbo.tblMusic_Artist.Artist, 
                      dbo.tblMusic_Song.AlbumID, dbo.tblMusic_Album.Album, dbo.tblMusic_Artist.Genre
FROM         dbo.tblMusic_Album RIGHT OUTER JOIN
                      dbo.tblMusic_Song ON dbo.tblMusic_Album.ID = dbo.tblMusic_Song.AlbumID LEFT OUTER JOIN
                      dbo.tblMusic_Artist ON dbo.tblMusic_Song.ArtistID = dbo.tblMusic_Artist.ID
ORDER BY dbo.tblMusic_Song.Song

For Inserts I have an ASPX based form that I want to insert information into these 3 tables:

tblSong:
PK-Identity
Song
ArtistID
AlbumID
SLink

tblAlbum:
PK-Identity
Album

tblArtist:
PK-Identity
Artist
Genre
NLink

How can I go about this with efficiency & consistency? Thanks for any help in advance!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-08 : 23:45:52

You can easily create an insert and an update stored procedure for this:

Check out Books Online for details on how to write them.

In short, the insert proc needs to:
Check if the artist exists in tblArtists. If not, create new record. If so, get the artist ID
Ditto for Album
Insert into Song table (using the ID's of the artist and album tables - TIP: use @@IDENTITY to get the ID of the last inserted record)

Similar for the update. Just run an update statement on each of the tables as required.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources