Home » SQL ServerRSS

Help with inserting data into table

For simplicity i have two tables Movie and Genre

The movie table has the following columns:
MovieID - PRIMARY KEY
MovieName
GenreID - FOREIGN KEY

The Genre table
GenreID - PRIMARY KEY
GenreName

What i am trying to do is create an INSERT command so that it inserts a movie name and the genre in one go. I also need to ensure the genre doesnt already exist  and if it does not to add it (this is not essential at present) but use it to insert the record with that genre. Could anyone advise how i should go about this? Im open to other suggestions but i would like this transaction done in one go?

Please note i can insert data using INSERT INTO Movie (MovieName....) Values ("Something") but struggle in getting the Genre as its a Foreign key.

Thanks in advance.

 

5 Answers Found

 

Answer 1

Please take a look at this blog post showing the exact same problem you're having troubles with. You just need to adjust it for your tables.

How to insert  information into multiple related tables  and return ID using SQLDataSource



Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
 

Answer 2

Thanks for the link. Although the information contained within that link is overwhelming i not sure if someone could base an example on the above tables. The link may provide me an answer but if its based on something i understand then i know why and when to do it and what is going on.

Thanks again

 

Answer 3

/*
CREATE TABLE [dbo].[Genre](
[GenreID] [int] IDENTITY(1,1) NOT NULL,
[GenreName] [NVARCHAR](50) NULL,
CONSTRAINT [PK_Genre] PRIMARY KEY CLUSTERED
([GenreID] ASC))
GO
CREATE TABLE [dbo].[Movie](
[MovieID] [int] IDENTITY(1,1) NOT NULL,
[MovieName] [NVARCHAR](50) NULL,
[GenreID] [int] NOT NULL,
 CONSTRAINT [PK_Movie] PRIMARY KEY CLUSTERED
([MovieID] ASC))
GO
ALTER TABLE [dbo].[Movie]  WITH CHECK ADD  CONSTRAINT [FK_Movie_Genre] FOREIGN KEY([GenreID])
REFERENCES [dbo].[Genre] ([GenreID])
GO
ALTER TABLE [dbo].[Movie] CHECK CONSTRAINT [FK_Movie_Genre]
GO

--DROP TABLE [dbo].[Movie]
--DROP TABLE [dbo].[Genre]
*/

DECLARE @GenreName [NVARCHAR](50)
DECLARE @MovieName [NVARCHAR](50)

SET @MovieName = 'Matrix'
SET @GenreName = 'Sci-Fi'

DECLARE @GenreID INT
SET @GenreID = (SELECT TOP 1 [GenreID] FROM [dbo].[Genre] WHERE GenreName = @GenreName)

IF @GenreID IS NULL
BEGIN
 INSERT [dbo].[Genre] VALUES (@GenreName)
 SET @GenreID = SCOPE_IDENTITY()
END
 INSERT [dbo].[Movie] VALUES (@MovieName,@GenreID) --TODO logic for duplicate movie, will leave that for you

SELECT * FROM [dbo].[Genre]
SELECT * FROM [dbo].[Movie]

 

 

 

 

Answer 4

Do you mean the blog is a bit hard to understand? Essentially, all that is needed is (pseudo - code)

declare @Output table  (ID int)  

begin transaction

insert into Genre (GenreName)  output Inserted.GenreID into @output values  (@GenreName)

insert into Movies (MovieName, GenreID) select @MovieName, O.ID from @Output O

commit transaction

Also handle error cases.

The blog post shows how to handle certain specific points relevant to exception handling and transaction  handling.

 


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
 

Answer 5

Do you mean the blog is a bit hard to understand? Essentially, all that is needed is (pseudo - code)

declare @Output table  (ID int)  

begin transaction

insert into Genre (GenreName)  output Inserted.GenreID into @output values  (@GenreName)

insert into Movies (MovieName, GenreID) select @MovieName, O.ID from @Output O

commit transaction

Also handle error cases.

The blog post shows how to handle certain specific points relevant to exception handling and transaction  handling.

 


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog


In short the blog was good and for me was difficult to understand as T-SQL is not my strong area so i needed that example but in short to grasp what was going on so any future challenges of such would allow me to resolve myself and along the way learn what is going on.

Thanks everyone for all your help

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter