CODEWITHSUNDEEP
sqlsql-server-2008insert
NaveenBhat
NaveenBhat

Reputation: 3318

Insert into from 2 different table

What I have: I have 3 tables namely CONTENT_MASTER, Account_Master and Content_Licenses. The Content_Licenses table is used to store the licences of each content for different accounts. For example, I have the content called myOldContent with the following licences for each account:

Table: Content_Licenses

CL_AccountID    NoOfLicences
8               99
241             70
240             30
186             30
99              30

What I want: For the below contents, I want to give same number of licences for each account :

Table: CONTENT_MASTER

CM_ID   CM_NAME
101    myNewContent1
102    myNewContent2
103    myNewContent3

What I did: I have written the below query to do this:

--Assign New components ID here and execute the query.
DECLARE @NewComponentID int

--Give license to new components
INSERT INTO CONTENT_LICENSES
                (CL_CM_ID
                ,CL_AccountID
                ,NoOfLicences)
            SELECT 
                @NewComponentID  --Want to fetch from CONTENT_MASTER table, instead of passing like this
                ,CL.CL_AccountID --Fetching from CONTENT_LICENSES table
                ,CL.NoOfLicences 
                FROM CONTENT_LICENSES CL 
                    INNER JOIN CONTENT_MASTER ON CM_ID = CL.CL_CM_ID
                WHERE CM_NAME = 'myOldContent'

Problem I need to execute this query 3 times to give licences to 3 new components(i.e. by assigning the ID of the new component and executing it). Is there any alternative way to do this(without using cursor)?

Upvotes: 0

Views: 392

Answers (1)

Andriy M
Andriy M

Reputation: 77657

Maybe I'm still missing some bits, but so far it seems like you could try something like this:

INSERT INTO CONTENT_LICENSES
    (CL_CM_ID
    ,CL_AccountID
    ,NoOfLicences)
SELECT 
    CM_NEW.CM_ID
    ,CL.CL_AccountID
    ,CL.NoOfLicences 
FROM CONTENT_LICENSES CL 
    INNER JOIN CONTENT_MASTER CM_OLD ON CM_OLD.CM_ID = CL.CL_CM_ID
    CROSS JOIN CONTENT_MASTER CM_NEW
WHERE CM_OLD.CM_NAME = 'myOldContent'
  AND CM_NEW.CM_NAME IN 
       ('myNewContent1'
       ,'myNewContent2'
       ,'myNewContent3')

Upvotes: 1

Related Questions