Help with Inserting a record - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Inserting a record

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-07-16 : 11:05:01

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

Thanks in advance.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-16 : 13:39:09

assuming genre is the first table to be inserted


INSERT INTO Genre ----
SELECT @GenreID =SCOPE_IDENTITY()

INSERT INTO Movies(MovieName, GenreID )
Values('Drunken Noodles',@GenreID )

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources