Update an existing table based on result of executing a variable

Question

I'm looking for some views on how to go about resolving this challenge. I have a variable say @Var1 which holds a SQL statement within it.

Example

@Var1 = `SELECT * from another_table WHERE City IS NOT NULL AND Address IS NOT NULL`

When I execute this variable

EXECUTE sp_executesql @Var1

I get the desired result, City and Address excluding NULL values.

I am hoping to update a existing table (tbl1) based on execution result of @Var1:

Something like:

UPDATE TABLE tbl1 AS (EXECUTE sp_executesql @Var1)

Is something like this even possible? Or what approach can I take to get the result of @Var1 into tbl1?

Thanks in advance.

*"update a existing table based on execution result"* what do you mean with this statement? You need joins to update against another result set. — EzLo, Jun 14 '18 at 11:23
as @EzLo writes. Then you need to join you result from var1 to your update table otherwise it makes no sense. — SqlKindaGuy, Jun 14 '18 at 11:36
I am hoping to update an existing table example:(tbl1) based on execution result of @Var1 as explained in my question. — Gary, Jun 14 '18 at 11:36
@Gary you are basically saying "I want to update [Table A] based on [Table B]". That is not enough information. — Jason Geiger, Jun 14 '18 at 12:24
@JasonGeiger What ever data I retrieve by executing Var1, I am hoping to store that into exiting table "tbl1" . Just wondering if this is a possibility or not possible within SQL? — Gary, Jun 14 '18 at 12:49
IT is possible. But you still need to join that result set onto your table. You could store your var1 result into a temp table and then join it to your tbl1 — SqlKindaGuy, Jun 15 '18 at 07:25

score 1 · Accepted Answer · answered Jun 14 '18 at 15:57

1

My apologies for including links for each step but they deserve the credit.

There are 2 steps. Insert into a temp-table then merge the data from that temp-table into your final table.

You will have to insert into a Temp Table first. INSERT INTO @TABLE EXEC @query with SQL Server 2000

Then you have to merge that data into you main table. SQL MERGE statement to update data

answered Jun 14 '18 at 15:57

Jason Geiger

1,912
18
32

Thanks for providing the answer, I would adapt this approach. – Gary Jun 15 '18 at 07:40

score 0 · Answer 2 · answered Jun 14 '18 at 11:36

try This

UPDATE  tbl1 
        SET Column1= b.Column1,
        SET Column2= b.Column2,
        SET Column3= b.Column3,
FROM    tbl1 a 
INNER JOIN (
SELECT  Column1,Column2,Column3
FROM    another_table WHERE City IS NOT NULL AND Address IS NOT NULL ) b ON   a.city_Id  = b.city_Id

Update an existing table based on result of executing a variable

2 Answers2