CODEWITHSUNDEEP
sql-servert-sqlsql-update
Shelan Patel
Shelan Patel

Reputation: 11

TSQL Update Statement Execute

enter image description here

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?

Upvotes: 1

Views: 56

Answers (2)

Joe Taras
Joe Taras

Reputation: 15379

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

Upvotes: 1

GarethD
GarethD

Reputation: 69769

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

Upvotes: 0

Related Questions