Dynamically Construct and Execute a SQL Server Statements

There's a table named "IndexFields" with fields "TableId", "DataField", and "PKField" (see below)

Table "IndexFields"
"TableId" "DataField" "PKField"
1 PartNumber PartId
1 PartId PartId
2 SupplierName SupplierId
etc.

I need code so that an On Update/On Insert trigger on the "Parts" table will essentially lookup all the records in "IndexFields" with TableId "1" and create and run SQL statements for each record found. (see below)

INSERT INTO IndexTable (TableId, PKId, FieldData) SELECT '1', PartId, PartNumber FROM INSERTED
INSERT INTO IndexTable (TableId, PKId, FieldData) SELECT '1', PartId, PartId FROM INSERTED

How can I do this, or what else can I do to achieve the same result? (Note: I'll want to add triggers for other tables with their corresponding table id's as well)

Microsoft SQL Server

Last Comment

Declan Basile

8/22/2022 - Mon

HainKurt

5/2/2017

maybe something like this:

for ix in
  select 'INSERT INTO IndexTable (TableId, PKId, FieldData) values (1, :new.' || PKField || ', :new.' || DataField || ')' sql
    from IndexFields where TableId = 1
loop
  dbms_output.put_line(ix.sql);
  --EXECUTE IMMEDIATE ix.sql;
end loop;

Open in new window

check output, if it is ok, then comment out line 5, uncomment 6

Declan Basile

5/3/2017

ASKER

What is the SQL Server equivalent to this Oracle code?

ASKER CERTIFIED SOLUTION

HainKurt

5/3/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Declan Basile

5/3/2017

ASKER

That helped greatly. Thank you. I just have to get past one more thing ...

@SQL = "INSERT INTO IndexedData (IndexedData, EntityId, PKId) SELECT CONVERT(varchar(200), X.PartId), 7, T.Id FROM @Tbl T INNER JOIN Parts X ON T.Id = X.PartId"

@Tbl is a table variable that isn't recognized within sp_executesql. Do you have any idea about how to get past this issue? Will sp_executesql recognize temporary tables?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

HainKurt

5/3/2017

maybe you should use global temp table

##Tbl or a real table...

you can populate the real table before this and truncate after if you wish...

select * into ##Tbl from @Tbl

then dynamis sql here

truncate / drop ##Tbl... or maybe you dont need to do anything...

or maybe you need to create ##Tbl once, and it will be in DB all the time, but it will be empty on each session...
not sure, you should read temp/global temp tables :)

Declan Basile

5/4/2017

ASKER

This worked ...

EXEC sp_executesql @SQL, N'@Tbl IdsTable READONLY', @Tbl

@Tbl is a User defined table type

Declan Basile

5/4/2017

ASKER

That was a tremendous help. You thank you very much!

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

HainKurt