November 20, 2002 at 1:43 am
Hi,
I am quite new to SQL Scripting, so be gentle please 🙂
I am writing an SQL Server Script (SQL2K) to update codes in various tables.
The codes are stored in a master table and are also present in various other
tables in the system.
Currently the script looks like this and handles the change of only two
codes - I may have a hundred in practice.
-- Declare Variables
DECLARE @OldVal1 AS Char(2)
DECLARE @NewVal1 AS Char(2)
DECLARE @OldVal2 AS Char(2)
DECLARE @NewVal2 AS Char(2)
-- Set Variables
SET @OldVal1 = 'A1'
SET @NewVal1 = 'AX'
SET @OldVal2 = 'B1'
SET @NewVal2 = 'BX'
-- ICCTAG (IC Category Codes)
UPDATE [ICCATG]
SET CATEGORY = @NewVal1
WHERE CATEGORY = @OldVal1
UPDATE [ICCATG]
SET CATEGORY = @NewVal2
WHERE CATEGORY = @OldVal2
-- ICITEM (IC Items)
UPDATE [ICITEM]
SET CATEGORY = @NewVal1
WHERE CATEGORY = @OldVal1
UPDATE [ICITEM]
SET CATEGORY = @NewVal2
WHERE CATEGORY = @OldVal2
Any help would be really appreciated.
Thanks
Paul
November 20, 2002 at 3:37 am
Put the values in a table and do something like
UPDATE ICCATG
SET CATEGORY = NewCategory
FROM MappingTable
WHERE MappingTable.OldCategory = ICCATG.Category
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 20, 2002 at 4:24 am
Hi Simon,
Thanks for teh feedback. The code works well except that on certain tables where 'CATEGORY' is the PK I get:
Server: Msg 2627, Level 14, State 1, Line 14
Violation of PRIMARY KEY constraint 'ICCATG_KEY_0'. Cannot insert duplicate key in object 'ICCATG'.
The statement has been terminated.
Is there a way around this based on the code you gave?
Thanks again,
Paul
November 20, 2002 at 7:57 am
If you have a primary key by design need to be unique, and SQL Server will not allow you to add a duplicate record. If the record is not truly a duplicate then you will need drop the primary key and add a composite key that will contain all the columns to uniquely identify the record.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 20, 2002 at 8:48 am
Hi Guys,
Thanks for the input on this. We have done some work on this ourselves now and have come up with the following. We will try and make it a little more dynamic by autocreating the table that stores the list of tables. The mapping table will still need populating manually (of course!!!)
-- MS-SQL 2000 T-SQL Script
-- ````````````````````````
-- This script file replaces the values of one field within
-- an entire database with new values.
-- The script requires that you have:
-- 1) A table named ICMAPTBL that contains the names
-- of all the tables that are affected.
-- 2) A table named ICCATMAP that contains two columns,
-- Col1 has old values, col2 has new values.
-- 3) The script assumes that the field name is consistent for all
-- tables within the database.
-- Decalre Vars
DECLARE @OldName varchar(8), @NewName varchar(8)
DECLARE @TBLName varchar(20)
DECLARE @vcSql VARCHAR(8000)
-- Declare Cursors & Build Record Sets
DECLARE icmap_cursor CURSOR FOR
SELECT * FROM ICCATMAP
DECLARE ictbl_cursor SCROLL CURSOR FOR
SELECT * FROM ICMAPTBL
-- Opens Cursors
OPEN icmap_cursor
OPEN ictbl_cursor
-- Retreive first set of Old and New Values
FETCH NEXT FROM icmap_cursor
INTO @OldName, @NewName
-- Loop through affeted tables and update values
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NewName = RTRIM(@NewName)
SET @OldName = RTRIM(@OldName)
PRINT 'Replacing Field Data... Old Value: ' + @OldName + ' with New Value: ' + @NewName
FETCH FIRST FROM ictbl_cursor
INTO @TBLName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vcSql = RTRIM(@TBLName)
PRINT 'Executing on Table: ' + @vcSql
PRINT ''
PRINT 'Executing Update Command:'
PRINT 'UPDATE ' + @vcSql + ' SET CATEGORY = ''' + @NewName + ''' WHERE CATEGORY = ''' + @OldName + ''''
EXEC ('UPDATE ' + @vcSql + ' SET CATEGORY = ''' + @NewName + ''' WHERE CATEGORY = ''' + @OldName + '''' )
FETCH NEXT FROM ictbl_cursor
INTO @TBLName
END
FETCH NEXT FROM icmap_cursor
INTO @OldName, @NewName
END
CLOSE icmap_cursor
CLOSE ictbl_cursor
DEALLOCATE icmap_cursor
DEALLOCATE ictbl_cursor
GO
November 20, 2002 at 9:51 am
Ok here goes. You will soon learn that CURSORs are bad, satans invention. Initially were only included in SQL Server cos ANSI standard required them. Put this way in 6.5 they weren't implemented very well, and haven't got much better.
Ok one argument says that if you are only going to do this once then whats wrong with a CURSOR. Well my answer is that it sets a mind set which people tend to follow when it is going more than once. i.e. digging a big hole with a spade even though you have a digger, but you don't know how to use a digger. If your only doing it once, learning to use the digger is going to be tough but if you are going to do it lots of times, which is going to be easier.
As a database person you have to think in sets. SET based operations out peform row by row operations by orders of magnitudes.
Sorry lecture over
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 20, 2002 at 10:07 am
Hi Simon,
Thanks for the feedback (I think).
We are, as I said, very new to writing SQL scripts. If using sets is the way to go how do we change the script to use them? We don't mind a little trial and error so pointing us to some articles on here or elsewhere that shows some of the methods used would be helpful.
Thanks
Paul
November 20, 2002 at 10:16 am
Simon
I did ask you before how you would therefore code a routine where something has to be done to each of a set of rows from a query, say for example calling a COM interface to export them from SQL - how would you code that without a cursor. They may be the work of the devil, but if the SQL language does not provide any other means of "do this to each of the result rows" then you have no alternative.
Regards
Simon (time to get confusing)
November 20, 2002 at 10:41 am
Simon true. you do have to use cursors, but you need to know when to use cursors. If you don't know try not to. Your example is one where you have to really use a cursor. Although you would probably be better firing the com object which then gets the data itself and loops though it.
As for the situation here, my initial thoughts are
DROP FOREIGN KEY
DROP PRIMARY KEY
UPDATE ICCATG
SET CATEGORY = NewCategory
FROM MappingTable
WHERE MappingTable.OldCategory = ICCATG.Category
UPDATE ICCITEM
SET CATEGORY = NewCategory
FROM MappingTable
WHERE MappingTable.OldCategory = ICCATG.Category
ADD PRIMARY KEY
ADD FOREIGN KEY.
or
--Remove catgeories that are not having their category changed, but would cause a duplicate
DELETE FROM ICCATG
WHERE EXISTS (SELECT 1
FROM MappingTable MT
WHERE MT.NewCategory = ICCATG.Category)
AND NOT EXISTS (SELECT 1
FROM MappingTable MT
WHERE MT.oldCategory = ICCATG.Category)
Then do the 2 updates.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply