TSQL Update Statement Execute

Question

I have a table with user defined fields i want to keep updated based on the another table. In order to do i created the following query

select 
    a + b + c + d + e + f + g + h 
from
    (select 
         'update gl00100 set USERDEF1 =' as a, 
         '''' + DMA + '''' as b, 
         ', set USERDEF2 =' as c, 
         '''' + Brand + '''' as d, 
         ', set USRDEFS1 =' as e,
         '''' + convert(char(10), dateopened, 120) + '''' as f, 
         'where actnumbr_2 =' as g,
         GPStore as h  
     from 
         [192.168.xxx.xx].bi.dbo.store 
     where 
         store <> 0000 and DateOpened is not null) x

As you can tell the query is building the update statements I want to run. How can I run the query and then run the results. Is that even possible?

You must use dynamic sql

Joe Taras
– Joe Taras

2017-11-30 14:25:43 +00:00
Commented Nov 30, 2017 at 14:25 — Joe Taras
– Joe Taras, Commented Nov 30, 2017 at 14:25

Joe Taras · Accepted Answer · 2017-12-03 16:50:10Z

1

Try this:

DECLARE @sql nvarchar(2000)
DECLARE #crs INSENSITIVE CURSOR FOR
SELECT 'update gl00100 set USERDEF1 =' as a, ''''+DMA+'''' as b, ', 
   set USERDEF2 =' as c, ''''+Brand+'''' as d, ', set USRDEFS1 =' as e,
   ''''+convert(char(10),dateopened,120)+'''' as f, 'where actnumbr_2 =' as g,
   GPStore as h  
from [192.168.xxx.xx].bi.dbo.store where store <> 0000 and DateOpened is not null 
OPEN #crs
FETCH NEXT FROM #crs INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @sql
    FETCH NEXT FROM #crs INTO @sql
END
CLOSE #crs
DEALLOCATE #crs

edited Dec 3, 2017 at 16:50

answered Nov 30, 2017 at 14:32

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

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

1 Comment

Joe Taras Over a year ago

@ShelanPatel: Because you're newbie. If my answer acomplish your aim please accept it

GarethD · Accepted Answer · 2017-11-30 14:45:08Z

You can do this using a JOIN rather than building Dynamic SQL Statements and executing them one by one:

UPDATE  g
SET     USERDEF1 = s.DMA,
        USERDEF2 = s.Brand,
        USRDEFS1 = s.DateOpened
FROM    gl00100 AS g
        INNER JOIN [192.168.xxx.xx].bi.dbo.store AS s
            ON s.GPStore = g.actnumbr_2
WHERE   s.Store <> 0000 
AND     s.DateOpened IS NOT NULL;

You may also find you get better performance using OPENQUERY, when using 4 part names for a cross server query, you can't take advantage of statistics as well, so you may end up pulling over the entire store table into memory, only to select a few rows at the end. So you might try something like this:

UPDATE  g
SET     USERDEF1 = s.DMA,
        USERDEF2 = s.Brand,
        USRDEFS1 = s.DateOpened
FROM    gl00100 AS g
        INNER JOIN OPENQUERY
        (   [192.168.xxx.xx], 
            'SELECT DMA, Brand, DateOpened, GPStore 
            FROM bi.dbo.store 
            WHERE store <> 0000 AND DateOpened is not null'
        ) AS s
            ON s.GPStore = g.actnumbr_2

Collectives™ on Stack Overflow

TSQL Update Statement Execute

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related