Mashing two tables
-
We have development and production databases. Developers add data to a dev database table and users add data to a production table that is identical in structure to the dev table. For each production deployment of new code, I need to mash these two tables together to retain data from both. The tables have primary keys that are Identity=Yes and they autoincrement.
For example, developers add a row to a dev table that has fields ID, data1, with results ID=456, data1=black; ID=457, data1=white. In the meantime, users add data to a similar production table with results ID=1345, data1=yellow; ID=1346, data1=blue; ID=1347, data1=white. I want to end up with a table in production that retains the records where ID=1345, data1=yellow; ID=1346, data1=blue; ID=1347, data1=white; while adding a new value ID=1348, data1=black.
Any help would be appreciated!
-
Great title!
Oh, if we only had the UPSERT command (DB2 - updates existing rows and inserts new ones).
Let me restate to see if I understand. Users can add new colors to the production database, and developers can add new colors to the dev database, which ultimately need to be transfered to the same list of colors that are in the production database?
Big problem I see is that you have to compare the color in order to add the new rows to the production database. So you could get weird things like "whiet" or "wite" or "wihet" existing as separate colors.
If I've understood the question, there ways to approach it. Here's one (syntax not guaranteed
):Insert into [production table color] where name not in (select name from [dev table color])
Did I follow your issue properly?
-
James,
Yes, you captured the essence. (I understand that UPSERT might be available in SQL Server 2005?)
I was able to do what I want, slightly enhanced, in SQL Server 2000 with the following:
/* This updates tblProd, a table in Production, with tblQA (a table from dev now in QA for testing) data, looking at a particular field to match against. For example, it could update a phone number difference by looking at a user id. */
UPDATE tblProd
SET data2 =
(SELECT data2
FROM tblQA as D
WHERE tblProd.data1 = D.data1)
where exists
(select * from tblQA as E
where tblProd.data1 = E.data1)
/* This inserts into tblProd any tblQA data that doesn't exist in tblProd, looking at a match field as above. For example, it could add a new user based on not finding a user id. Any unchanged rows that exist in tblProd will remain as is, with their existing IDs, preserving any relationships. */
INSERT INTO tblProd(data1,data2)
SELECT data1,data2
FROM tblQA
WHERE data1
NOT IN (SELECT data1 FROM tblProd)
/* Assumption: tblQA has valid tested data. */
Thanks for your help; it pointed me in the right direction!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply