Insert - 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

Insert

Author

Topic

Fu
Starting Member

23 Posts

Posted - 2005-09-30 : 08:15:37

CREATE TABLE Country(
CountryID int not null PRIMARY KEY,
Country varchar(100) not null
)
CREATE TABLE Artist(
ArtistID int not null PRIMARY KEY,
ArtistPhoto image ,
Artist varchar(100),
CountryID int not null,
FOREIGN KEY (CountryID) REFERENCES Country(CountryID)
)
CREATE TABLE Track(
TrackID int not null PRIMARY KEY,
Track varchar(100)
)
CREATE TABLE ArtistTrack(
ArtistID int not null,
TrackID int not null,
PRIMARY KEY (ArtistID,TrackID),
FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID),
FOREIGN KEY (TrackID) REFERENCES Track(TrackID)
)
CREATE TABLE Label(
LabelID int not null PRIMARY KEY,
Label varchar(100) not null
)
CREATE TABLE Shelf(
ShelfID int not null PRIMARY KEY,
Shelf char(5) not null
)

CREATE TABLE CD(
CDID bigint not null PRIMARY KEY,
CDTitle varchar(100) not null,
CDType varchar(100) not null,
CDDate datetime,
CDCover image,
ShelfID int not null,
LabelID int not null,
FOREIGN KEY (ShelfID) REFERENCES Shelf(ShelfID),
FOREIGN KEY (LabelID) REFERENCES Label(LabelID)
)
CREATE TABLE CDTrack(
CDID bigint not null,
TrackID int not null,
PRIMARY KEY (CDID,TrackID),
FOREIGN KEY (CDID) REFERENCES CD(CDID),
FOREIGN KEY (TrackID) REFERENCES Track(TrackID)
);
-----------------------------------------------------------------------
1-I have created the following database. I need to insert information into this database and i wondering about the best to way to insert a new CD to the database. That is, can i insert values for CD columns along with other information related to the CD such as Tracks of the CD + The Artist/Band that sings the Track? or I have to insert every table columns seperately.
2-In table CD. is there a way to insert the Label directly instead of inserting the LabelID.How?

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 08:35:42

You can't really insert into multiple tables with one INSERT statement (you could create a VIEW with an INSTEAD OF TRIGGER, but that's probably a BAD idea!).

You could create a Stored Procedure that did this - but it would first create the record in the CD table, and then each corresponding row in the CDTrack / Track tables.

However, a Stored Procedures that had additional parameters for things like the Track Name would be a good idea. Either you provide the TrackID or the Track Name. If you don;t provide the TrackID then create a new Track and use the TrackID from that record.

Kristen

Fu
Starting Member

23 Posts

Posted - 2005-09-30 : 08:40:57

can you please elaborate because it's still cloudy for me.
thanx

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 09:06:14

"can you please elaborate"
Hopefully this may help!


CREATE PROCEDURE dbo.usp_CD_INSERT
	@CDID bigint,
	@CDTitle varchar(100),
	@CDType varchar(100),
	@CDDate datetime,
	@CDCover image,
	@ShelfID int,
	@LabelID int,
	-- Subsidiary data
	@Shelf char(5)=NULL,	-- If NOT NULL then a new Shelf will be created
	@Label varchar(100)=NULL	-- If NOT NULL then a new Label will be created
AS
BEGIN
SET NOCOUNT ON
	BEGIN TRANSACTION
	IF @Shelf IS NOT NULL
	BEGIN
		INSERT INTO dbo.Shelf
		(
			ShelfID, Shelf
		) VALUES
		(
			@ShelfID, @Shelf
		)
		IF @@ERROR <> 0 GOTO ERROR_ROLLBACK
	END

	IF @Label IS NOT NULL
	BEGIN
		INSERT INTO dbo.Label
		(
			LabelID, Label
		) VALUES
		(
			@LabelID, @Label
		)
		IF @@ERROR <> 0 GOTO ERROR_ROLLBACK
	END

	IF NOT EXISTS (SELECT * FROM dbo.Shelf WHERE ShelfID = @ShelfID)
	BEGIN
		-- (1)
		SELECT [ERROR] = 'Shelf record not found', [@ShelfID]=@ShelfID
		GOTO ERROR_ROLLBACK
	END
	IF NOT EXISTS (SELECT * FROM dbo.Label WHERE LabelID = @LabelID)
	BEGIN
		-- (1)
		SELECT [ERROR] = 'Label record not found', [@LabelID]=@LabelID
		GOTO ERROR_ROLLBACK
	END
	IF EXISTS (SELECT * FROM dbo.CD WHERE CDID = @CDID)
	BEGIN
		-- (1)
		SELECT [ERROR] = 'CS record ALREADY exists', [@CDID]=@CDID
		GOTO ERROR_ROLLBACK
	END

	INSERT INTO dbo.CD
	(
		CDID, CDTitle, CDType, CDDate, CDCover, ShelfID, LabelID
	) VALUES
	(
		@CDID, @CDTitle, @CDType, @CDDate, @CDCover, @ShelfID, @LabelID
	)
	IF @@ERROR <> 0 GOTO ERROR_ROLLBACK

	COMMIT
	RETURN

ERROR_ROLLBACK:
	ROLLBACK
	-- (1)
	SELECT [ERROR] = 'Transaction rolled back'
	RETURN
END
GO

(1) You might want to use RAISERROR, OUTPUT variable or a RETURN value instead of a RecordSet for error reporting

-- Error - ShelfID does not yet exist
EXEC	dbo.usp_CD_INSERT
	@CDID = 1,
	@CDTitle = 'My greatest hits',
	@CDType = 'Popular Music',
	@CDDate = '30-Sep-2005',
	@CDCover = NULL,
	@ShelfID = 1,
	@LabelID = 1
GO

-- Error - ShelfID provided, but LabelID does not exist - should be NO Shelf Record created
EXEC	dbo.usp_CD_INSERT
	@CDID = 1,
	@CDTitle = 'My greatest hits',
	@CDType = 'Popular Music',
	@CDDate = '30-Sep-2005',
	@CDCover = NULL,
	@ShelfID = 1,
	@LabelID = 1,
	-- Subsidiary data
	@Shelf = 'ABC12'
GO

-- SHould be NO rows
SELECT * FROM Shelf WHERE ShelfID = 1
GO

-- ALl data now provided
EXEC	dbo.usp_CD_INSERT
	@CDID = 1,
	@CDTitle = 'My greatest hits',
	@CDType = 'Popular Music',
	@CDDate = '30-Sep-2005',
	@CDCover = NULL,
	@ShelfID = 1,
	@LabelID = 1,
	-- Subsidiary data
	@Shelf = 'ABC12',
	@Label = 'KristenGram'
GO

SELECT * FROM Shelf WHERE ShelfID = 1
SELECT * FROM Label WHERE LabelID = 1
SELECT * FROM CD WHERE CDID = 1
GO

-- Tidy up!
DROP PROCEDURE dbo.usp_CD_INSERT
GO

(1) = You might want to use RAISERROR, OUTPUT variable or a RETURN value instead of a RecordSet for error reporting

Kristen

Fu
Starting Member

23 Posts

Posted - 2005-09-30 : 09:35:27

thanx alot i will check it out

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-30 : 23:45:31

Fu, is this from a training book? It looks like examples in some of the beginning ASP.NET books.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Fu
Starting Member

23 Posts

Posted - 2005-10-02 : 03:31:36

no derrick it's part of a search engine made to music megastores so that customers searches for CD without refering to workers in the store who are supposed to be mostly cut off and replaced with lcd's for search ;)
anyways i will need asp.net in this work
------------------------------------------------
the problem persisted coz in this procedure shelfid and shelf [name] are meant to be specific--->shelf names will be physically limited
abcde fghij klmno pqrst uvxyz 01...89
in this mean there's no need to create shelves coz they are already assigned...
however when executing this procedure there's one row affected [result] on select queries [o rows are returned]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources