title: SQL insert data into table has foreign key
body: <p>Background: SQL Server Mgmt Studio 2008 R2</p>
  
  <pre><code>DataTable: ProductID(PK, bigint), ProductNameID(FK, int), Price(Decimal)
  
  ProductNameTable: ProductNameID(PK, int), ProductName(varchar)
  </code></pre>
  
  <p>So I need to insert data like "123.4, AAA" (Price, ProductName) into DataTable but with ProductNameID is a FK,</p>
  
  <p>How can I do that???? Bec ProductNameID is auto-increment</p>
  
  <p>This is the code I have so far:</p>
  
  <pre><code>CREATE PROCEDURE DataInsertSP        
  @Price DECIMAL(8, 2),    @ProductName VARCHAR(20)       
  AS
  BEGIN     
  
  DECLARE @NewProductNameID INT    
  
  INSERT INTO ProductNameTable(ProductName)    
  VALUES (@ProductName)    
  
  SELECT @NewProductNameID = SCOPE_IDENTITY()    
  
  INSERT INTO DataTable(ProductNameID, Price)    
  VALUES (@NewProductNameID, @Price);
  END;
  
  EXECUTE DataInsertSP 
  @Price = 12.25        ,   @ProductName = 'Argo'
  </code></pre>
  
  <p>Please give me some advice, Thanks so much</p>