Using exec() function when inserting to a table

Question

When you have 2 tables table like this

CREATE TABLE #BranchType
(
    [External_BranchTypeID] [uniqueidentifier] DEFAULT newsequentialid()  NOT NULL,
    [BranchTypeID] [smallint] identity(1,1) ,
    [BranchTypeDescription] [nvarchar](20) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [UserCreated] [nvarchar](20) NOT NULL,
    [DateModified] [datetime] NULL,
    [UserModified] [nvarchar](20) NULL,
    [IsDeleted] [bit] NOT NULL,
)

CREATE TABLE BranchSubType
(
    [External_BranchSubTypeID] [uniqueidentifier] DEFAULT newsequentialid()  NOT NULL,
    [BranchSubTypeID] [smallint] identity(1,1) ,
    [BranchTypeID] [uniqueidentifier] NOT NULL,
    [BranchSubTypeDescription] [nvarchar](30) NOT NULL,
    [FinancialSystemTypeId] [smallint] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [UserCreated] [nvarchar](20) NOT NULL,
    [DateModified] [datetime] NULL,
    [UserModified] [nvarchar](20) NULL,
    [IsDeleted] [bit] NOT NULL,
)

How can you do an insert like the one below in SQL Server? I am trying to return the guid value

DECLARE @SQLCmd VARCHAR(max)
set @SQLCmd = 'SELECT External_BranchTypeID FROM   #BranchType WHERE BranchTypeID =1'

INSERT INTO BranchSubType (BranchTypeID, BranchSubTypeDescription, BranchSubTypeId,  DateCreated, UserCreated,IsDeleted) 
VALUES ( exec(@SQLCmd), 'Normal',1, getdate(), 'System',0) --FROM   #BranchType A WHERE A.BranchTypeID = 1

valex · Accepted Answer · 2013-07-03 13:02:02Z

1

In this case you don't need to use EXEC

INSERT INTO BranchSubType 
    (BranchTypeID, 
     BranchSubTypeDescription, 
     BranchSubTypeId,  
     DateCreated, 
     UserCreated,
     IsDeleted) 
SELECT External_BranchTypeID,
       'Normal',
        1, 
        getdate(), 
        'System',
        0  
 FROM   #BranchType WHERE BranchTypeID =1

answered Jul 3, 2013 at 13:02

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using exec() function when inserting to a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related