September 8, 2010 at 8:15 pm
Hello All,
I need a secord (or third or fourth) set of eyes on this; it's driving me crazy.
I'm trying to write a query that updates a table from another table. My goal is to update records that currently exists in table1 with chances for the same record found in table2. If there are new records in table2 not found in table1, then I want to insert those new records found in table2 into table1.
My update portion of my query works fine, but the insert is not working. I am including a copy of my query. The source table is SERVERS_VT (temp table) and the destination table is SERVERS_V.
I manually made a change to an existing record and added a new record in the source table. I ran my query and the existing record in the destination changed (update statement) but the new record does not get added.
Thanks to all for your imput...
Ronnie
UPDATE SERVERS_V
SET SERVERS_V.SERVER_ID = SERVERS_VT.SERVER_ID,
SERVERS_V.SERVER_NAME = SERVERS_VT.SERVER_NAME,
SERVERS_V.SERVER_MAKE = SERVERS_VT.SERVER_MAKE,
SERVERS_V.SERVER_MODEL = SERVERS_VT.SERVER_MODEL,
SERVERS_V.GROUP_NAME = SERVERS_VT.GROUP_NAME,
SERVERS_V.SUBGROUP_NAME = SERVERS_VT.SUBGROUP_NAME,
SERVERS_V.STREET = SERVERS_VT.STREET,
SERVERS_V.CITY = SERVERS_VT.CITY,
SERVERS_V.STATE = SERVERS_VT.STATE,
SERVERS_V.ZIP = SERVERS_VT.ZIP,
SERVERS_V.CATEGORY_NAME = SERVERS_VT.CATEGORY_NAME,
SERVERS_V.PIM_SERVER_NAME = SERVERS_VT.PIM_SERVER_NAME,
SERVERS_V.SERVER_ROLE = SERVERS_VT.SERVER_ROLE,
SERVERS_V.FQDN = SERVERS_VT.FQDN,
SERVERS_V.ASSET_TAG = SERVERS_VT.ASSET_TAG,
SERVERS_V.ACQ_METHOD = SERVERS_VT.ACQ_METHOD,
SERVERS_V.LEASE_START_DATE = SERVERS_VT.LEASE_START_DATE,
SERVERS_V.LEASE_END_DATE = SERVERS_VT.LEASE_END_DATE,
SERVERS_V.STATUS_NAME = SERVERS_VT.STATUS_NAME,
SERVERS_V.SERIAL_NUMBER = SERVERS_VT.SERIAL_NUMBER,
SERVERS_V.PRIMARY_IP = SERVERS_VT.PRIMARY_IP,
SERVERS_V.REMOTE_ACCESS_IP = SERVERS_VT.REMOTE_ACCESS_IP,
SERVERS_V.SERVER_DOMAIN = SERVERS_VT.SERVER_DOMAIN,
SERVERS_V.MB_MEMORY = SERVERS_VT.MB_MEMORY,
SERVERS_V.STORAGE_TOTAL = SERVERS_VT.STORAGE_TOTAL,
SERVERS_V.STORAGE_FREE = SERVERS_VT.STORAGE_FREE,
SERVERS_V.STORAGE_USED = SERVERS_VT.STORAGE_USED,
SERVERS_V.FUNCTION_NAMES = SERVERS_VT.FUNCTION_NAMES,
SERVERS_V.PROCESSOR_NAME = SERVERS_VT.PROCESSOR_NAME,
SERVERS_V.PROCESSOR_COUNT = SERVERS_VT.PROCESSOR_COUNT,
SERVERS_V.PROCESSOR_SPEED = SERVERS_VT.PROCESSOR_SPEED,
SERVERS_V.ASSET_PAYMENT = SERVERS_VT.ASSET_PAYMENT,
SERVERS_V.OS_VER = SERVERS_VT.OS_VER,
SERVERS_V.SERVICE_PACK = SERVERS_VT.SERVICE_PACK,
SERVERS_V.PATROL_INSTALLED = SERVERS_VT.PATROL_INSTALLED,
SERVERS_V.PATROL_VERSION = SERVERS_VT.PATROL_VERSION,
SERVERS_V.ORDER_NUMBER = SERVERS_VT.ORDER_NUMBER,
SERVERS_V.REQUEST_NUMBER = SERVERS_VT.REQUEST_NUMBER,
SERVERS_V.VPMO_NUM = SERVERS_VT.VPMO_NUM,
SERVERS_V.BUILD_SA = SERVERS_VT.BUILD_SA,
SERVERS_V.APP_ACRONYM = SERVERS_VT.APP_ACRONYM,
SERVERS_V."APP_NAME" = SERVERS_VT."APP_NAME",
SERVERS_V.TSM_INSTALLED = SERVERS_VT.TSM_INSTALLED,
SERVERS_V.CLIENT_VERSION = SERVERS_VT.CLIENT_VERSION,
SERVERS_V.UPDATE_BY = SERVERS_VT.UPDATE_BY,
SERVERS_V.UPDATE_DATE = SERVERS_VT.UPDATE_DATE,
SERVERS_V.NETBACKUP_AGENT_INSTALLED = SERVERS_VT.NETBACKUP_AGENT_INSTALLED,
SERVERS_V.ORIG_COMP_NAME = SERVERS_VT.ORIG_COMP_NAME,
SERVERS_V.SERVER_TIMEZONE = SERVERS_VT.SERVER_TIMEZONE
FROM SERVERS_VT
JOIN SERVERS_V
ON SERVERS_VT.SERVER_ID = SERVERS_V.SERVER_ID
WHERE SERVERS_VT.SERVER_ID = SERVERS_V.SERVER_ID
IF @@ROWCOUNT = 0
INSERT INTO SERVERS_V
(
SERVER_ID,
SERVER_NAME,
SERVER_MAKE,
SERVER_MODEL,
GROUP_NAME,
SUBGROUP_NAME,
STREET,
CITY,
STATE,
ZIP,
CATEGORY_NAME,
PIM_SERVER_NAME,
SERVER_ROLE,
FQDN,
ASSET_TAG,
ACQ_METHOD,
LEASE_START_DATE,
LEASE_END_DATE,
STATUS_NAME,
SERIAL_NUMBER,
PRIMARY_IP,
REMOTE_ACCESS_IP,
SERVER_DOMAIN,
MB_MEMORY,
STORAGE_TOTAL,
STORAGE_FREE,
STORAGE_USED,
FUNCTION_NAMES,
PROCESSOR_NAME,
PROCESSOR_COUNT,
PROCESSOR_SPEED,
ASSET_PAYMENT,
OS_VER,
SERVICE_PACK,
PATROL_INSTALLED,
PATROL_VERSION,
PATCH_GROUP_NAME,
RELEASE_GROUP,
ORDER_NUMBER,
REQUEST_NUMBER,
VPMO_NUM,
BUILD_SA,
APP_ACRONYM,
"APP_NAME",
TSM_INSTALLED,
CLIENT_VERSION,
UPDATE_BY,
UPDATE_DATE,
NETBACKUP_AGENT_INSTALLED,
ORIG_COMP_NAME,
SERVER_TIMEZONE
)
SELECT
SERVER_ID,
SERVER_NAME,
SERVER_MAKE,
SERVER_MODEL,
GROUP_NAME,
SUBGROUP_NAME,
STREET,
CITY,
STATE,
ZIP,
CATEGORY_NAME,
PIM_SERVER_NAME,
SERVER_ROLE,
FQDN,
ASSET_TAG,
ACQ_METHOD,
LEASE_START_DATE,
LEASE_END_DATE,
STATUS_NAME,
SERIAL_NUMBER,
PRIMARY_IP,
REMOTE_ACCESS_IP,
SERVER_DOMAIN,
MB_MEMORY,
STORAGE_TOTAL,
STORAGE_FREE,
STORAGE_USED,
FUNCTION_NAMES,
PROCESSOR_NAME,
PROCESSOR_COUNT,
PROCESSOR_SPEED,
ASSET_PAYMENT,
OS_VER,
SERVICE_PACK,
PATROL_INSTALLED,
PATROL_VERSION,
PATCH_GROUP_NAME,
RELEASE_GROUP,
ORDER_NUMBER,
REQUEST_NUMBER,
VPMO_NUM,
BUILD_SA,
APP_ACRONYM,
"APP_NAME",
TSM_INSTALLED,
CLIENT_VERSION,
UPDATE_BY,
UPDATE_DATE,
NETBACKUP_AGENT_INSTALLED,
ORIG_COMP_NAME,
SERVER_TIMEZONE
FROM SERVERS_VT
WHERE NOT EXISTS
(select
SERVERS_VT.SERVER_ID,
SERVERS_VT.SERVER_NAME,
SERVERS_VT.SERVER_MAKE,
SERVERS_VT.SERVER_MODEL,
SERVERS_VT.GROUP_NAME,
SERVERS_VT.SUBGROUP_NAME,
SERVERS_VT.STREET,
SERVERS_VT.CITY,
SERVERS_VT.STATE,
SERVERS_VT.ZIP,
SERVERS_VT.CATEGORY_NAME,
SERVERS_VT.PIM_SERVER_NAME,
SERVERS_VT.SERVER_ROLE,
SERVERS_VT.FQDN,
SERVERS_VT.ASSET_TAG,
SERVERS_VT.ACQ_METHOD,
SERVERS_VT.LEASE_START_DATE,
SERVERS_VT.LEASE_END_DATE,
SERVERS_VT.STATUS_NAME,
SERVERS_VT.SERIAL_NUMBER,
SERVERS_VT.PRIMARY_IP,
SERVERS_VT.REMOTE_ACCESS_IP,
SERVERS_VT.SERVER_DOMAIN,
SERVERS_VT.MB_MEMORY,
SERVERS_VT.STORAGE_TOTAL,
SERVERS_VT.STORAGE_FREE,
SERVERS_VT.STORAGE_USED,
SERVERS_VT.FUNCTION_NAMES,
SERVERS_VT.PROCESSOR_NAME,
SERVERS_VT.PROCESSOR_COUNT,
SERVERS_VT.PROCESSOR_SPEED,
SERVERS_VT.ASSET_PAYMENT,
SERVERS_VT.OS_VER,
SERVERS_VT.SERVICE_PACK,
SERVERS_VT.PATROL_INSTALLED,
SERVERS_VT.PATROL_VERSION,
SERVERS_VT.PATCH_GROUP_NAME,
SERVERS_VT.RELEASE_GROUP,
SERVERS_VT.ORDER_NUMBER,
SERVERS_VT.REQUEST_NUMBER,
SERVERS_VT.VPMO_NUM,
SERVERS_VT.BUILD_SA,
SERVERS_VT.APP_ACRONYM,
SERVERS_VT."APP_NAME",
SERVERS_VT.TSM_INSTALLED,
SERVERS_VT.CLIENT_VERSION,
SERVERS_VT.UPDATE_BY,
SERVERS_VT.UPDATE_DATE,
SERVERS_VT.NETBACKUP_AGENT_INSTALLED,
SERVERS_VT.ORIG_COMP_NAME,
SERVERS_VT.SERVER_TIMEZONE
FROM SERVERS_VT
JOIN SERVERS_V
ON SERVERS_VT.SERVER_ID = SERVERS_V.SERVER_ID
WHERE
SERVERS_V.SERVER_ID = SERVERS_VT.SERVER_ID AND
SERVERS_V.SERVER_NAME = SERVERS_VT.SERVER_NAME AND
SERVERS_V.SERVER_MAKE = SERVERS_VT.SERVER_MAKE AND
SERVERS_V.SERVER_MODEL = SERVERS_VT.SERVER_MODEL AND
SERVERS_V.GROUP_NAME = SERVERS_VT.GROUP_NAME AND
SERVERS_V.SUBGROUP_NAME = SERVERS_VT.SUBGROUP_NAME AND
SERVERS_V.STREET = SERVERS_VT.STREET AND
SERVERS_V.CITY = SERVERS_VT.CITY AND
SERVERS_V.STATE = SERVERS_VT.STATE AND
SERVERS_V.ZIP = SERVERS_VT.ZIP AND
SERVERS_V.CATEGORY_NAME = SERVERS_VT.CATEGORY_NAME AND
SERVERS_V.PIM_SERVER_NAME = SERVERS_VT.PIM_SERVER_NAME AND
SERVERS_V.SERVER_ROLE = SERVERS_VT.SERVER_ROLE AND
SERVERS_V.FQDN = SERVERS_VT.FQDN AND
SERVERS_V.ASSET_TAG = SERVERS_VT.ASSET_TAG AND
SERVERS_V.ACQ_METHOD = SERVERS_VT.ACQ_METHOD AND
SERVERS_V.LEASE_START_DATE = SERVERS_VT.LEASE_START_DATE AND
SERVERS_V.LEASE_END_DATE = SERVERS_VT.LEASE_END_DATE AND
SERVERS_V.STATUS_NAME = SERVERS_VT.STATUS_NAME AND
SERVERS_V.SERIAL_NUMBER = SERVERS_VT.SERIAL_NUMBER AND
SERVERS_V.PRIMARY_IP = SERVERS_VT.PRIMARY_IP AND
SERVERS_V.REMOTE_ACCESS_IP = SERVERS_VT.REMOTE_ACCESS_IP AND
SERVERS_V.SERVER_DOMAIN = SERVERS_VT.SERVER_DOMAIN AND
SERVERS_V.MB_MEMORY = SERVERS_VT.MB_MEMORY AND
SERVERS_V.STORAGE_TOTAL = SERVERS_VT.STORAGE_TOTAL AND
SERVERS_V.STORAGE_FREE = SERVERS_VT.STORAGE_FREE AND
SERVERS_V.STORAGE_USED = SERVERS_VT.STORAGE_USED AND
SERVERS_V.FUNCTION_NAMES = SERVERS_VT.FUNCTION_NAMES AND
SERVERS_V.PROCESSOR_NAME = SERVERS_VT.PROCESSOR_NAME AND
SERVERS_V.PROCESSOR_COUNT = SERVERS_VT.PROCESSOR_COUNT AND
SERVERS_V.PROCESSOR_SPEED = SERVERS_VT.PROCESSOR_SPEED AND
SERVERS_V.ASSET_PAYMENT = SERVERS_VT.ASSET_PAYMENT AND
SERVERS_V.OS_VER = SERVERS_VT.OS_VER AND
SERVERS_V.SERVICE_PACK = SERVERS_VT.SERVICE_PACK AND
SERVERS_V.PATROL_INSTALLED = SERVERS_VT.PATROL_INSTALLED AND
SERVERS_V.PATROL_VERSION = SERVERS_VT.PATROL_VERSION AND
SERVERS_V.PATCH_GROUP_NAME = SERVERS_VT.PATCH_GROUP_NAME AND
SERVERS_V.RELEASE_GROUP = SERVERS_VT.RELEASE_GROUP AND
SERVERS_V.ORDER_NUMBER = SERVERS_VT.ORDER_NUMBER AND
SERVERS_V.REQUEST_NUMBER = SERVERS_VT.REQUEST_NUMBER AND
SERVERS_V.VPMO_NUM = SERVERS_VT.VPMO_NUM AND
SERVERS_V.BUILD_SA = SERVERS_VT.BUILD_SA AND
SERVERS_V.APP_ACRONYM = SERVERS_VT.APP_ACRONYM AND
SERVERS_V."APP_NAME" = SERVERS_VT."APP_NAME" AND
SERVERS_V.TSM_INSTALLED = SERVERS_VT.TSM_INSTALLED AND
SERVERS_V.CLIENT_VERSION = SERVERS_VT.CLIENT_VERSION AND
SERVERS_V.UPDATE_BY = SERVERS_VT.UPDATE_BY AND
SERVERS_V.UPDATE_DATE = SERVERS_VT.UPDATE_DATE AND
SERVERS_V.NETBACKUP_AGENT_INSTALLED = SERVERS_VT.NETBACKUP_AGENT_INSTALLED AND
SERVERS_V.ORIG_COMP_NAME = SERVERS_VT.ORIG_COMP_NAME AND
SERVERS_V.SERVER_TIMEZONE = SERVERS_VT.SERVER_TIMEZONE)
September 8, 2010 at 8:21 pm
Your INSEERT statement will only run if the @@ROWCOUNT = 0.
Here is the excerpt from your code. If the UPDATE updates any rows, the INSERT won't run:
IF @@ROWCOUNT = 0
INSERT INTO SERVERS_V
(
September 8, 2010 at 10:29 pm
Hi,
Why don't you try this instead of using IF @@ROWCOUNT = 0:
IF EXISTS (SELECT *
FROM SERVERS_VT
LEFT OUTER JOIN SERVERS_V
ON SERVERS_VT.SERVER_ID = SERVERS_V.SERVER_ID
WHERE SERVERS_V.SERVER_ID IS NULL
)
INSERT INTO SERVERS_V
(
...
This will check if any records exists in SERVERS_VT that is not in SERVERS_V based on the LEFT OUTER JOIN returning NULL values where there is no data to select next to SERVERS_VT.
September 8, 2010 at 11:44 pm
Mariza van Wyk (9/8/2010)
Hi,Why don't you try this instead of using IF @@ROWCOUNT = 0:
IF EXISTS (SELECT *
FROM SERVERS_VT
LEFT OUTER JOIN SERVERS_V
ON SERVERS_VT.SERVER_ID = SERVERS_V.SERVER_ID
WHERE SERVERS_V.SERVER_ID IS NULL
)
INSERT INTO SERVERS_V
(
...
This will check if any records exists in SERVERS_VT that is not in SERVERS_V based on the LEFT OUTER JOIN returning NULL values where there is no data to select next to SERVERS_VT.
Why even have the IF, you just end up duplicating the logic for checking the existance of records. The WHERE clause will identify what records don't exist in the destination table and allow them to be inserted.
September 9, 2010 at 1:13 am
I also realised that, but only after I had already submitted the post. 🙂
No check is necessary if using the LEFT OUTER JOIN, and the INSERT can follow the UPDATE statement directly.
September 9, 2010 at 8:29 am
Just wanted to say Thank You to everyone for your suggestions. I tried what Lynn had suggested and that seems to have done the trick. Sooo simple...
Anyway, thanks again!!!
Ronnie
September 9, 2010 at 8:49 am
The check for existence looks way too complicated - this should work:
INSERT INTO SERVERS_V
(
SERVER_ID,
SERVER_NAME,
SERVER_MAKE,
.
.
.
SERVER_TIMEZONE
)
SELECT
SERVER_ID,
SERVER_NAME,
SERVER_MAKE,
.
.
.
SERVER_TIMEZONE
FROM SERVERS_VT
WHERE NOT EXISTS (SELECT 1 FROM SERVERS_V WHERE SERVER_ID = SERVERS_VT.SERVER_ID)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply