T-SQL HEADACHES. UPDATES, INSERTS AND APPENDS

Question

T-SQL HEADACHES. UPDATES, INSERTS AND APPENDS

lara31

SSCommitted

Points: 1802
More actions
February 2, 2005 at 6:33 am

#59897

I AM TRYING TO ADD DATA TO A TABLE, FROM ANOTHER TABLE. I HAVE ABOUT 5 KEY FEILDS THAT WILL ALWAYS BE THESAME INFORMATION SSN, DOB ETC,
I WANT TO CHECK THE INFORMATION IN TABLE WITH THE INFORMATION IN TABLE B, IF DATA IN THE KEY FEILDS MATCH, THEN
VERIFY THE DATA IN THE REMAINING SIX FIELDS.
   IF
       THE INFORMATION IN TABLE A IS DIFFERENT FROM THE INFORMATION IN TABLE B, (THE INCOMING TABLE),
     THEN
        UPDATE THE EXISTING FIELDS IN TABLE (WITH THE EXCEPTION OF THE KEY FIELDS) WITH THE INFORMATION FROM TABLE B
ELSE
RETAIN THE EXISTING DATA IN TABLE A.

IF THERE IS NO EXISTING RECORD (NO MATCHES TO THE KEY FEILDS)
THEN INSERT THE NEW RECORD FROM TABLE B.

HELP!!!! 9 I HAVE BEEN AT THIS ALL WEEK.

[font="Comic Sans MS"][/font]It is what it is.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Charles Otten Default port Points: 1475 More actions · Answer 1

I would suggest doing this in two steps.

First step is the update process. Don't worry about if the non-key fields match or not because if they match the update won't change the data anyway.

As far as the insert something like this should work

insert into table a (field list)

select b.* from b left join a on a.ssn = b.ssn and a.dob = b.dob etc.

where a.ssn is null

Hope this helps and if you have questions let me know.

If the phone doesn't ring...It's me.

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 2

I just went through this headache....the following will work with a few tweaks for your specific needs.

------------------------

If Object_Id('tempdb..#tmpPVS') is Not Null

DROP TABLE [dbo].[#tmpPVS]

CREATE TABLE #tmpPVS

(

ViewDate datetime,

PageID nvarchar(255),

CountryCode varchar(2),

LanguageCode varchar(2),

ViewCount int,

)

INSERT INTO #tmpPVS

SELECT convert(datetime,(convert(nvarchar,TPV.ViewDate, 103)), 103) AS ViewDate, TPV.PageID AS PageID,TPV.CountryCode AS CountryCode,TPV.LanguageCode AS LanguageCode, COUNT(*) AS ViewCount

FROM tableTPV TPV

GROUP BY convert(datetime,(convert(nvarchar,TPV.ViewDate, 103)), 103), TPV.PageID, TPV.CountryCode, TPV.LanguageCode

UPDATE dbo.tablePV

SET dbo.tablePV.ViewDate = tmpPV.ViewDate,

dbo.tablePV.PageID = tmpPV.PageID,

dbo.tablePV.CountryCode = tmpPV.CountryCode,

dbo.tablePV.LanguageCode = tmpPV.LanguageCode,

dbo.tablePV.ViewCount = dbo.tablePV.ViewCount+tmpPV.ViewCount

FROM #tmpPVS tmpPV WHERE

dbo.tablePV.ViewDate = tmpPV.ViewDate

AND dbo.tablePV.PageID = tmpPV.PageID

AND dbo.tablePV.CountryCode = tmpPV.CountryCode

AND dbo.tablePV.LanguageCode = tmpPV.LanguageCode

INSERT INTO dbo.tablePV (ViewDate,PageID,CountryCode,LanguageCode,ViewCount)

SELECT DISTINCT tmpPV.ViewDate, tmpPV.PageID, tmpPV.CountryCode, tmpPV.LanguageCode, tmpPV.ViewCount

FROM #tmpPageViews tmpPV

LEFT JOIN dbo.tablePV PV

ON (PV.ViewDate = tmpPV.ViewDate

AND PV.PageID = tmpPV.PageID

AND PV.CountryCode = tmpPV.CountryCode

AND PV.LanguageCode = tmpPV.LanguageCode)

WHERE PV.ViewDate IS NULL AND PV.PageID IS NULL AND PV.CountryCode IS NULL AND PV.LanguageCode IS NULL

lara31 SSCommitted Points: 1802 More actions · Answer 3

Thanks a lot guys!!!. i will try both suggestion .

[font="Comic Sans MS"][/font]It is what it is.