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.
| Author |
Topic |
|
frankivey
Starting Member
2 Posts |
 Posted - 2007-04-06 : 13:30:42
|
I am trying to write a complex insert statement and not really sure how to get started.First I am building an application to a record label to add artist, songs, pictures, video, and music to their site.Thanks to asp.net 2.0 building the application interface was easy, but now I realize I have run into a brick wall and need some help.in one application, called "add artist album"in this application you the administrator is adding information to three different tables (artist (holds all info about the artist), album (holds all the info about artist artist album), and artistalbum (joins the association of artist to album))I have no clue how to really start writing an insert statement for this.Basically I want to be able to insert the album data (album name and release date) and also have a drop down box to allow the administrator to choose an artist from the artist table that is associated with the album. That association is built together in the artist artistalbum table (artistid, albumid, artistalbumid).Please can someone point me in the right direction. I am thinking that the insert statement will be something like and insert with nested select statement and inner join included but i haven't a clue how to do this.Please Help!!!!!!!!!!!!!!!!  learning sql all over again |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
frankivey
Starting Member
2 Posts |
Posted - 2007-04-06 : 16:45:31
|
| I am trying to write a complex insert statement and not really sure how to get started.First I am building an application to a record label to add artist, songs, pictures, video, and music to their site.Thanks to asp.net 2.0 building the application interface was easy, but now I realize I have run into a brick wall and need some help.in one application, called "add artist album"in this application you the administrator is adding information to three different tables (artist (holds all info about the artist), album (holds all the info about artist artist album), and artistalbum (joins the association of artist to album))I have no clue how to really start writing an insert statement for this.Basically I want to be able to insert the album data (album name and release date) and also have a drop down box to allow the administrator to choose an artist from the artist table that is associated with the album. That association is built together in the artist artistalbum table (artistid, albumid, artistalbumid).Please can someone point me in the right direction. I am thinking that the insert statement will be something like and insert with nested select statement and inner join included but i haven't a clue how to do this.My table DDLCreate table artist (artistid int prmrykey, artistname varchar (100), bio varchar(100))Create table album (albumid int prmrykey, albumname varchar (100), releasedate date)Create table artistalbum ( artistalbumid int prmrykey, artistid int, albumid int)My DML for the insert statement so far:ALTER PROCEDURE sp_AddArtistAlbum @AlbumName varchar(50), @ReleasedDate datetime, @ArtistID intAS SET NOCOUNT ON DECLARE @AlbumID INT INSERT INTO tb_Album (AlbumName, ReleasedDate) VALUES (@AlbumName, @ReleasedDate) Like I sadi I know I am supposed to have a table join and and select statement that is nested to really make this thing work but I do not know where to start, can someone please help me. I hope I have provided enough information.My expected results are to insert data from into the album table and have that data associated with an artist chosen in the drop down box.Please Help!!!!!!!!!!!!!!!! learning sql all over again |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-06 : 17:22:28
|
Your table structur is bad. You should only use twotables. Also you are way overcomplicating this. I made the tables with identity columns as their ID, so everytime a new record is added the ID will automatically be generated. (This is just for example purposes, you may want to assign your own id's) CREATE TABLE [dbo].[album]( [albumid] [int] IDENTITY(1,1) NOT NULL, [ArtistID] [int] NULL, [albumname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [releasedate] [datetime] NULL, CONSTRAINT [PK_album] PRIMARY KEY CLUSTERED ( [albumid] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[album] WITH CHECK ADD CONSTRAINT [FK_album_artist] FOREIGN KEY([ArtistID])REFERENCES [dbo].[artist] ([artistid])ALTER TABLE [dbo].[album] CHECK CONSTRAINT [FK_album_artist]CREATE TABLE [dbo].[artist]( [artistid] [int] IDENTITY(1,1) NOT NULL, [artistname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [bio] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_artist] PRIMARY KEY CLUSTERED ( [artistid] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[artist] WITH CHECK ADD CONSTRAINT [FK_artist_artist] FOREIGN KEY([artistid])REFERENCES [dbo].[artist] ([artistid])ALTER TABLE [dbo].[artist] CHECK CONSTRAINT [FK_artist_artist]ALTER PROCEDURE sp_AddArtistAlbum @AlbumName varchar(50),@ReleasedDate datetime,@ArtistID intASSET NOCOUNT ON INSERT INTO tb_Album( ArtistID,AlbumName, ReleasedDate)VALUES (@ArtistID, @AlbumName, @ReleasedDate)/* --Use the below query to view the relationSelect *from TB_Artist a inner join TB_Album b o a.ArtistID = b.ArtistID*/ Hope this helps. |
|
|
|
|
|
|
|
|
Newbie needing help