none
INSERT INTO using SELECT with OUTPUT INTO - multi-part identifier could not be bound

    Question

  • CREATE TABLE #tblFileTypeTmp (NewFileTypeID INT,
    						ExistingFileTypeID INT)
    
    INSERT INTO MyDB.dbo.tblFileType (FileType,
    									   DecodeMethod) 
    OUTPUT inserted.FileTypeID,
    	  cf.FileTypeID -- The multi-part identifier "cf.FileTypeID" could not be bound.
    	  INTO #tblFileTypeTmp
    SELECT cf.FileType,
    	  cf.DecodeMethod
      FROM AnotherDB.dbo.tblFileType AS cf
    For whatever reason the cf.FileTypeID can't be bound.  I am 110% sure that column exists on the table AnotherDB.dbo.tblFileType.  CERTAIN.  I just want to put it in ExistingFileTypeID in the temp table!  Any thoughts?
    Wednesday, July 17, 2013 4:06 PM

Answers

  • You can't use columns from the SELECT part of an INSERT ... SELECT in the OUTPUT clause.  The only columns an INSERT statement can use in the OUTPUT clause is columns in the inserted pseudo table.  Fortunately, there is a way around this restriction.  Use MERGE to simulate an INSERT by using an ON condition that is always false.  So

    MERGE INTO MyDB.dbo.tblFileType
    USING AnotherDB.dbo.tblFileType AS cf
    ON 1 = 0
    WHEN NOT MATCHED THEN
      INSERT(FileType, DecodeMethod) Values(cf.FileType, cf.DecodeMethod)
    Output inserted.FileTypeID, cf.FileTypeID INTO 
      #tblFileTypeTmp(NewFileTypeID, ExistingFileTypeID);

    Tom

    Wednesday, July 17, 2013 4:38 PM

All replies

  • You can only uise inserted values (or deleted) for OUTPUT statement.

    So inserted.FileTypeID is ok.

    If you were updating a table you could get the old id using deleted.FileTypeID.

    Some more info here... http://msdn.microsoft.com/en-us/library/ms177564.aspx

    Wednesday, July 17, 2013 4:25 PM
  • You can't use columns from the SELECT part of an INSERT ... SELECT in the OUTPUT clause.  The only columns an INSERT statement can use in the OUTPUT clause is columns in the inserted pseudo table.  Fortunately, there is a way around this restriction.  Use MERGE to simulate an INSERT by using an ON condition that is always false.  So

    MERGE INTO MyDB.dbo.tblFileType
    USING AnotherDB.dbo.tblFileType AS cf
    ON 1 = 0
    WHEN NOT MATCHED THEN
      INSERT(FileType, DecodeMethod) Values(cf.FileType, cf.DecodeMethod)
    Output inserted.FileTypeID, cf.FileTypeID INTO 
      #tblFileTypeTmp(NewFileTypeID, ExistingFileTypeID);

    Tom

    Wednesday, July 17, 2013 4:38 PM
  • You can only uise inserted values (or deleted) for OUTPUT statement.

    So inserted.FileTypeID is ok.

    If you were updating a table you could get the old id using deleted.FileTypeID.

    Some more info here... http://msdn.microsoft.com/en-us/library/ms177564.aspx

    I don't think that's true.  Check out example E in the link you sent.  There's a "p.Name" used in the INTO portion of the statement.
    Wednesday, July 17, 2013 5:10 PM
  • You can't use columns from the SELECT part of an INSERT ... SELECT in the OUTPUT clause.  The only columns an INSERT statement can use in the OUTPUT clause is columns in the inserted pseudo table.  Fortunately, there is a way around this restriction.  Use MERGE to simulate an INSERT by using an ON condition that is always false.  So

    MERGE INTO MyDB.dbo.tblFileType
    USING AnotherDB.dbo.tblFileType AS cf
    ON 1 = 0
    WHEN NOT MATCHED THEN
      INSERT(FileType, DecodeMethod) Values(cf.FileType, cf.DecodeMethod)
    Output inserted.FileTypeID, cf.FileTypeID INTO 
      #tblFileTypeTmp(NewFileTypeID, ExistingFileTypeID);

    Tom

    Given the example E in the MSDN article that obrienkev referenced I'm still not sure why my script won't work.  However, this work around works beautifully.  Thanks Tom!

    Wednesday, July 17, 2013 5:17 PM
  • Given the example E in the MSDN article that obrienkev referenced I'm still not sure why my script won't work.

    The documentation in BOL on that page does say (or anyway, imply) you can't do what you were trying to do, but it's not clearly stated.  In the syntax definition at the top of the page, it says you can OUTPUT columns from the INSERTED table, the DELETED table, or a "from_table_name".  So the question is: what is a "from_table_name"?

    If you scroll down to the "Arguments" section, you will see it says: "from_table_name is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement ..."  So you can't use the FROM clause in an INSERT ... SELECT ... FROM ... statement.  It would be better (IMO) if the documentation explicitly said you can't do it with an INSERT.  So example E works because it is doing a DELETE, not an INSERT.

    Tom

    Wednesday, July 17, 2013 5:35 PM
  • Thanks for the clarification.  I did notice that there was a similar example for UPDATE and DELETE, but not INSERT.  Was hoping that it didn't matter.  Thanks so much!
    Wednesday, July 17, 2013 5:39 PM