Well, lets see shall we? 😉
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
*/
Hope that answers your question!