December 14, 2016 at 5:13 pm
Hi,
I'm working on a stored procedure that takes two parameters--the names of two databases--one that has a settings table and the other that has the table the settings will be applied to. Let's call the two tables "tbl_settings" in database1 and "tbl_servers" in database2
What I want to do is read each of the record IDs from tbl_settings and check whether they exist in tbl_servers. If they do, then update the values in tbl_servers based on what's in tbl_settings. If the record ID doesn't exist, then insert the row from tbl_settings into tbl_servers.
Here's a basic skeleton of what I have:
DECLARE @SERVER_ID VARCHAR(50);
DECLARE @SERVER_CURSOR CURSOR;
SET @SERVER_CURSOR = CURSOR FAST_FORWARD FOR
SELECT SERVER_ID
FROM <db parameter 1>.dbo.tbl_settings;
OPEN @SERVER_CURSOR;
FETCH NEXT FROM @SERVER_CURSOR INTO @SERVER_ID;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE t_serv
SET t_serv.SERVER_NAME = t_set.SERVER_NAME
FROM <db parameter 2>.dbo.tbl_servers t_serv
INNER JOIN <db parameter 1>.dbo.tbl_settings t_set
ON t_serv.SERVER_ID = t_set.SERVER_ID
WHERE t_serv.SERVER_ID = @SERVER_ID;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO <db parameter 2>.dbo.tbl_servers
(SERVER_ID,
SERVER_NAME)
SELECT SERVER_ID,
SERVER_NAME
FROM <db parameter 1>.dbo.tbl_settings
WHERE SERVER_ID = @SERVER_ID;
end
COMMIT TRAN
FETCH NEXT FROM @SERVER_CURSOR INTO @SERVER_ID;
END
CLOSE @SERVER_CURSOR;
DEALLOCATE @SERVER_CURSOR;
My issue is with how I can allow for the database names to be passed in, both for the cursor declaration and for the update and insert statements within the cursor itself. What would be the best, most secure way? I think I'm stuck using dynamic SQL, but can/should I only make parts of this dynamic SQL or should I put the entire cursor and the transaction within it in a large string, and execute that as dynamic SQL?
Also open to alternate ways altogether. I wasn't planning on manipulating the tbl_settings table, so that's why I didn't add a number column or anything for use with a while loop. As it stands, its structure exactly matches the tbl_servers structure, and I was hoping to keep that, but I could make changes if it means the code will be way better off.
Any thoughts? Thanks in advance.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 14, 2016 at 5:54 pm
Mike,
Why do you need a cursor for this? Can't you just do a basic upsert? Here's some fun reading for you:
December 14, 2016 at 8:40 pm
pietlinden (12/14/2016)
Mike,Why do you need a cursor for this? Can't you just do a basic upsert? Here's some fun reading for you:
pietlinden,
This is incredibly helpful. Much easier to use. Thanks for sharing.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply