November 23, 2015 at 2:47 pm
Hi,
can someone help me in writting a cursor with following update statements .This statement will updates each database in an instance of SQL .
UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
November 23, 2015 at 2:52 pm
cmc123 (11/23/2015)
Hi,can someone help me in writting a cursor with following update statements .This statement will updates each database in an instance of SQL .
UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
No need for a cursor, use sp_msForEachDB.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 23, 2015 at 2:56 pm
I written like this but it say's a syntax error but i dont have any clue where this syntax error is
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'US'.
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN USE ? EXEC(''UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
'') END'
EXEC sp_MSforeachdb @command
November 23, 2015 at 3:02 pm
cmc123 (11/23/2015)
Hi,can someone help me in writting a cursor with following update statements .This statement will updates each database in an instance of SQL .
UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
I can help you write an update statement but you do NOT need a cursor for this. I would recommend reworking your update statement to an UPDATE from. Then we can utilize some dynamic sql to run this on all databases on the instance. Of course you will want some additional conditions because you don't really want this to run on every database unless it has both of those tables. 😉
Something like this should get you started. You will want to modify the where statement somewhat to include/exclude whatever databases are relevant.
declare @SQL nvarchar(max) = '';
select @SQL = @SQL +
'UPDATE p
SET USERTXT1 =
CASE
WHEN A.CountryCode IN (''US'',''USA'') THEN ''US'' + A.Admin1Code
WHEN A.CountryCode IN (''CA'',''CAN'') THEN ''CA'' + A.Zone1
ELSE A.CountryCode
END
FROM dbo.Address A
join dbo.Property p on A.AddressID = p.AddressID;'
from sys.databases
--where name in (SomeListOfValidDatabases)
--where name not in (SomeListOfInvalidDatabases)
select @SQL
--uncomment the following when you have evaluated the dynamic sql and understand what query is going to run on your system
--exec sp_executesql @SQL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 23, 2015 at 3:03 pm
Phil Parkin (11/23/2015)
cmc123 (11/23/2015)
Hi,can someone help me in writting a cursor with following update statements .This statement will updates each database in an instance of SQL .
UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
No need for a cursor, use sp_msForEachDB.
There are times that this will sometimes simply skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 23, 2015 at 3:17 pm
Here's code that will generate the code you need.
Press Ctrl-T before running the code to have it come out as text. Then copy that text to a new query window. If you want/need to, you could even break it into segments so some dbs could run at the same time.
--Press Ctrl-T before running.
DECLARE @sql_template varchar(8000)
DECLARE @rowcount int
SET @sql_template = '
IF EXISTS(SELECT 1 FROM [$db$].sys.tables t WHERE t.name = ''Address'')
AND EXISTS(SELECT 1 FROM [$db$].sys.tables t WHERE t.name = ''Property'')
BEGIN
UPDATE p
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN (''US'',''USA'') THEN ''US''+Admin1Code
WHEN CountryCode IN (''CA'',''CAN'') THEN ''CA''+Zone1
ELSE CountryCode
END
FROM [$db$].dbo.Property P
INNER JOIN [$db$].dbo.Address A ON A.AddressID = dbo.Property.AddressID)
END
GO
'
SET NOCOUNT ON
SELECT REPLACE(@sql_template, '$db$', CAST(name AS varchar(128))) AS [--sql_commands]
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY name
SET @rowcount = @@ROWCOUNT
PRINT '--' + CAST(@rowcount AS varchar(10)) + ' rows updated.'
SET NOCOUNT OFF
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 24, 2015 at 12:47 am
Sean Lange (11/23/2015)
Phil Parkin (11/23/2015)
cmc123 (11/23/2015)
Hi,can someone help me in writting a cursor with following update statements .This statement will updates each database in an instance of SQL .
UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
No need for a cursor, use sp_msForEachDB.
There are times that this will sometimes simply skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Thanks for the link, Sean. I'd seen it before & then forgotten about it. I haven't yet seen a clear explanation as to why databases sometimes get missed out. But then again, given the alternative you provided, why waste time on it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 24, 2015 at 7:32 am
Phil Parkin (11/24/2015)
Sean Lange (11/23/2015)
Phil Parkin (11/23/2015)
cmc123 (11/23/2015)
Hi,can someone help me in writting a cursor with following update statements .This statement will updates each database in an instance of SQL .
UPDATE dbo.Property
SET USERTXT1 =
(SELECT
CASE
WHEN CountryCode IN ('US','USA') THEN 'US'+Admin1Code
WHEN CountryCode IN ('CA','CAN') THEN 'CA'+Zone1
ELSE CountryCode
END
FROM dbo.Address A
WHERE A.AddressID = dbo.Property.AddressID)
No need for a cursor, use sp_msForEachDB.
There are times that this will sometimes simply skip databases. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Thanks for the link, Sean. I'd seen it before & then forgotten about it. I haven't yet seen a clear explanation as to why databases sometimes get missed out. But then again, given the alternative you provided, why waste time on it.
Yeah I would be curious what causes it. Looking at some of the people stumped by it suggests I will never figure it out. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2015 at 3:06 am
Thanks for all. I have done few modifications and able to achive the goal
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply