SQL INSERT data based on a column of another table

You can select data to insert based on a table's data as so:

Insert into ProductKeys(ProductKey , ProductID , Price)
select p.productName, p.productId, 1 -- whatever your price is
from products p
where p.productName = 'testkey'

Sir, can I ask you , can this be extended? for example! If the ProductName does not exist the command won't work for sure. But if in case when ProductName does not exist ALSO insert a NEW product in Products table with the selected ProductName and also create the ProductKey. Can it be extended with a 'not exist'? Thanks a bunch sir! You already helped me alot!

I don't think you could do this in a single statement - maybe you could use an on before insert trigger, but I think something like that would break down with batch inserts. (but don't quote me on this, never tried it)

You could however break it up into multiple statements like this:

declare @productName varchar(50), @productId int, @price int

select 
        @productName = 'someNameThatDoesntExist', 
        @price = 5 -- whatever your price is.

if not exists (     -- check if the product name exists in the products table, if not create it
    select 1 
    from products 
    where productName = @productName
)
    begin
        insert into products (productName)
        select @productName

        select @productId = @@identity
    end
else -- the product already exists in the products table, so lookup its ID
    select @productId = productId
    from products
    where productName = @productName

Insert into ProductKeys(ProductKey , ProductID , Price)
select @productName, @productId, @price