CODEWITHSUNDEEP
sqlsql-servert-sql
Aarion
Aarion

Reputation: 35

INSERT VALUE from SELECT Statement

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

Upvotes: 0

Views: 84

Answers (1)

jordanwillis
jordanwillis

Reputation: 10705

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.

Upvotes: 1

Related Questions