June 4, 2010 at 1:17 pm
I would like to merge two records that exist in a single table. I currently have all of the code in VBA however I would like to somehow move this to T-SQL. The idea is that I have a user select a master record as well as the child record. I can pass the two values to a stored procedure however what I want to accomplish is to loop through all of the table for the master record and if the value is blank then get the value from the child record and insert that value.
I want to do this dynamically by querying the system table and loop through the fields using a WHILE loop.
Has anyone ever done this and if so can you offer some guidance. I am posting my vba code so that you can see what I am trying to accomplish:
Function UpdateEMPLOYEES(chEMP_ID As Integer, msEMP_ID As Integer)
On Error GoTo UpdateEMPLOYEES_err
Dim rstchild As New ADODB.Recordset, rstmaster As New ADODB.Recordset
Dim i As Integer
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
i = 1
rstchild.Open "SELECT * FROM dbo.EMPLOYEES WHERE EMP_ID= " & chEMP_ID & " ", cnn, adOpenStatic, adLockOptimistic
If rstchild.EOF = False Then
rstmaster.Open "SELECT * FROM dbo.EMPLOYEES WHERE EMP_ID =" & msEMP_ID & " ", cnn, adOpenStatic, adLockOptimistic
If rstmaster.EOF = False Then
Do While i < rstchild.Fields.Count
If (IsNull(rstmaster(i)) = True Or rstmaster(i) = "") And (IsNull(rstchild(i)) = False And rstchild(i) <> "") Then rstmaster(i) = rstchild(i)
i = i + 1
Loop
rstmaster.Update
End If
End If
UpdateEMPLOYEE_EXIT:
Exit Function
UpdateUpdateEMPLOYEE_ERR:
MsgBox Err.Number, Err.Description
Resume
End Function
Your help is greatly appreciated!
Owen White
June 7, 2010 at 3:51 am
Hello,
my opinion is that if you want to loop through columns, you should leave the code where it is, in VB.
If you want to write SQL code that will do this, you could write set-based code, something like:
UPDATE emp
SET col1=ISNULL(NULLIF(emp.col1,''), emp2.col1,
col2=ISNULL(NULLIF(emp.col2,''), emp2.col2
FROM employees emp
JOIN employees emp2 ON emp2.id = @id_slave
WHERE emp.id = @id_master
... it does just one update over all columns, but requires you to either explicitly add the SET code for each column, or use dynamic sql.
Maybe better solution would be to evaluate the differences in VB and then send one UPDATE statement that modifies data - which is what you already have, as far as I can see (no experience in VB, I just understand the general basics). Why did you want to do this in SQL?
June 7, 2010 at 7:05 am
I really appreciate the reply to my message. I actually just found a similar request in another post that does the same thing that you posted.
DECLARE @t TABLE (Id INT, C1 VARCHAR(100), C2 VARCHAR(100))
INSERT INTO @t
SELECT 1, 'foo', 'world'
UNION ALL SELECT 2, 'hello', NULL
DECLARE @Primary INT
DECLARE @Secondary INT
SELECT
@Primary = 2,
@Secondary = 1
UPDATE t1 SET
C1 = ISNULL(t1.C1, t2.C1),
C2 = ISNULL(t1.C2, t2.C2)
FROM @t t1
CROSS JOIN (SELECT * FROM @t WHERE Id = @Secondary) t2
WHERE t1.Id = @Primary
DELETE FROM @t WHERE Id = @Secondary
SELECT * FROM @t
My next question is how can I dynamically fill in the fields. I found a query that I can pass to the sysobjects and grab the field names. I would prefer not have to type all of the field names. any help you can give would be greatly appreciated.
Owen White
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply