USE TestingDB
DECLARE @sqlcmd    AS VARCHAR(4000)
DECLARE @theErrNbr AS VARCHAR(500)
CREATE TABLE table1 (
  colA   VARCHAR(50),
  colB   VARCHAR(50))
CREATE TABLE table2 (
  Acol   VARCHAR(50),
  Bcol   VARCHAR(50))
INSERT INTO table2(Acol,Bcol)
          SELECT 'Jack','Jill'
UNION ALL SELECT 'Jack','Beanstalk'
UNION ALL SELECT 'Queen','Hearts'

--Working version
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO table1'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM table2'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
SELECT * FROM table1
SELECT @theErrNbr AS 'ERROR'
/*Output
colA                                               colB
-------------------------------------------------- --------------------------------------------------
Jack                                               Jill
Jack                                               Beanstalk
Queen                                              Hearts

ERROR
--------------------------------------------------
0
*/
--Cleanup
DROP TABLE table1
CREATE TABLE table1 (
  colA   VARCHAR(50),
  colB   VARCHAR(50))
DROP TABLE table2
--Not working version due to lack of table2
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO table1'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM table2'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
SELECT * FROM table1
SELECT @theErrNbr AS 'ERROR'
--Cleanup
DROP TABLE table1
/*Ouput
Msg 208, Level 16, State 1, Line 1
Invalid object name 'table2'.
colA                                               colB
-------------------------------------------------- --------------------------------------------------


ERROR
--------------------------------------------------
208
*/