INSERT VALUE from SELECT Statement

Question

I want to update values to an audit table (dbo.Audit) prior to updating the same data column.

I have a SELECT statement to retrieve the values (which is created using dynamic SQL) stored in table dbo.[RuleSet], column [SelectStatement].

Issue: I am not sure how to update the Audit table.

CREATE TABLE [dbo].[Audit]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UpdateID] [int] NULL,
    [TableName] [varchar](250) NULL,
    [Orig] [varchar](250) NULL
)

CREATE TABLE [dbo].[RuleSet]
(
    [UpdateID] [int] IDENTITY(1,1) NOT NULL,
    [VID] [int] NULL,
    [TableName] [varchar](250) NOT NULL,
    [SetColumn] [varchar](250) NOT NULL,
    [SetValue] [varchar](250) NOT NULL,
    [WhereClause] [varchar](256) NULL,
    [SelectStatement] [varchar](4000) NULL
)

INSERT [dbo].[RuleSet] ([UpdateID], [VID], [TableName], [SetColumn],       [SetValue], [WhereClause], [SelectStatement]) 
VALUES (1, 1, N'TableA', N'ColumnA', N'10', N'ColumnA > 10', N'SELECT ColumnA FROM TableA WHERE ColumnA > 10')

INSERT [dbo].[RuleSet] ([UpdateID], [VID], [TableName], [SetColumn], [SetValue], [WhereClause], [SelectStatement]) 
VALUES (3, 2, N'TableB', N'ColumnB', N'20', N'ColumnB > 20', N'SELECT ColumnB FROM TableB WHERE ColumnB > 20')
GO

The logic of the code I am trying to achieve is:

INSERT INTO [dbo].[Audit]([UpdateID], [TableName], [Orig])
    SELECT 
        [UpdateID], [TableName],
        --Value returned from  executing the SELECT statement in column[SelectStatement] 
    FROM 
        dbo.[RuleSet]

Thank you

jordanwillis · Accepted Answer · 2017-03-19 07:26:41Z

1

You can use EXECUTE sp_executesql to execute [SelectStatement] and store the result in a temp table or a variable. Then use that as a sub query to insert into [dbo].[Audit].

You could make it a lot easier on yourself if you stored your query in [SelectStatement] like this.

N'SELECT ColumnB INTO #TempB FROM TableB WHERE ColumnB > 20'

Then you can just execute it using sp_executesql and select from TempB for the insert.

EXECUTE sp_executesql (SELECT [SelectStatement] FROM [dbo].[RuleSet] where [UpdateID] = ?);

INSERT INTO  [dbo].[Audit ] ([UpdateID], [TableName], [Orig])
SELECT [UpdateID], [TableName], #TempB.*
FROM dbo.[RuleSet], #TempB
WHERE [UpdateID] = ?

Note, my example is just a general suggestion and may need tweaking to execute.

answered Mar 19, 2017 at 7:26

jordanwillis

10.7k1 gold badge40 silver badges42 bronze badges

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

6 Comments

Aarion Over a year ago

Thank you for your suggestion. Will this method not create a temporary table for each row? N'SELECT ColumnB INTO #TempA FROM TableA WHERE ColumnB > 20' N'SELECT ColumnB INTO #TempB FROM TableB WHERE ColumnB > 20' Is there a way to do this in a set based approach?

jordanwillis Over a year ago

Does each [SelectStatement] only ever return 1 row and 1 col? If so then I would drop the temp table approach and return to a variable instead? The broad where condition in each query made it seem like they could return multiple rows each. Also you could use the same temp table for all and include an id col in the temp table too. Then you have 1 temp table with set based operations.

Aarion Over a year ago

Hi Jordan .. you are correct it could return more than one row but should only ever be the one column. But the [UpdateID] column would also have to be included into the temp table.

jordanwillis Over a year ago

Do the tables referenced in the [SelectStatement] queries have the [UpdateID] column too

Aarion Over a year ago

No they do not. But I will have to add in the associated [UpdateID] to the rows returned

|

Collectives™ on Stack Overflow

INSERT VALUE from SELECT Statement

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest