Inserting into two tables. - 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 2005 Forums

Transact-SQL (2005)

Inserting into two tables.

Author

Topic

Gribbler
Starting Member

1 Post

Posted - 2009-02-04 : 09:47:59

Hi, Im very new to SQL but need to write a query to do the following. Using MS SQL Server 2005.

Profile DefinitioninProfile Definition
------ ------------------- ----------
ProfileID DefinitionID DefinitionID
ProfileType ProfileID DefinitionType
ProfileName

In the definition table, defintion Type can be TypeA, TypeB ..... TypeZ. I want to make sure that for a certain profile type, ProfileTypeA the Definition has all the Types, TypeA -> TypeZ.

But some of the types already exist in the table, and I dont want to have duplicates.

So its something like
SELECT ProfileID from Profile where ProfileType = ProfileTypeA
FOR EACH ProfileID
IF NOT EXISTS IN Defintion TypeA
INSERT TypeA into Definition
INSERT ProfileID, DefinitionID into DefinitionInProfile

...repeat for TypeB, TypeC...
END
I need to get all the instances where ProfileType = ProfileTypeA

Then get the first Profile.profileID

Then check the DefinitioninProfile table to get a list of DefinitionIDs where the profileID = Profile.ProfileID

Then for all those Definition IDs check if there is a definitionType called 'TypeA' if not insert it, if there is ignore it. Then do the same for 'TypeB', repeat for typec, .. typeZ

Go back to step 2 and get the next Profile.ProfileID and repeat 3 & 4 for that profile ID.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-04 : 09:55:21

I'm not clear exactly what you need but I think what you could do is

1) Build your query as you suggested (SELECT ProfileID from Profile where ProfileType = ProfileTypeA)

Use this as a derived table and LEFT JOIN this to your definition table and only insert the lines where you get a NULL.

That's a bad explanation.... sorry

If you post the structure of your tables and some sample data with the required result I can give you better instructions!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 10:01:05

[code]
INSERT INTO DefinitioninProfile (DefinitionID,ProfileID)
SELECT m.DefinitionID,m.ProfileID
FROM
(
SELECT p.ProfileID,d.DefinitionID
FROM Profile p
CROSS JOIN Definition d
WHERE p.ProfileType='ProfileTypeA'
)m
LEFT JOIN DefinitioninProfile dp
ON dp.ProfileID =m.ProfileID
AND dp.DefinitionID =m.DefinitionID
WHERE dp.ProfileID IS NULL
AND dp.DefinitionID IS NULL
[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-04 : 10:07:14

Yeah -- something like that.

Visakh ++

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources