Insert SQL Script help needed

Jeff S
Jeff S used Ask the Experts™
on
I need assistance writing a SQL script. I need to pull in values from my DoctorFacility table into my cusLedgerCode table where the DoctorFacilityId is not found within the cusLedgerCode table. The values I need in the LastDate are '2006-12-31 00:00:00.000' and '2007-12-31 00:00:00.000'.

So I need to insert 2 entrys for every DoctorFacilityId not found in the cusLedgerCode table. One for the 2006 date and one for the 2007 date. DoctorFacilityId, ListName and Ledger are all values in my DoctorFacility table that will need to pull into the cusLedgerCode

cusLedgerCode

LastDate
DoctorFacilityId
ListName
Ledger

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
insert into cusLedgerCode
SELECT a.dt, DoctorFacilityId ,ListName, Ledger
FROM DoctorFacility  d
CROSS JOIN  (SELECT '2007-12-31' as dt UNION SELECT '2006-12-31' ) a
 WHERE NOT EXISTS (SELECT 1 FROM cusLedgerCode c WHERE  c.DoctorFacilityId = d.DoctorFacilityId )
I need to pull in values from my DoctorFacility table into my cusLedgerCode table where the DoctorFacilityId is not found within the cusLedgerCode table. The values I need in the LastDate are '2006-12-31 00:00:00.000' and '2007-12-31 00:00:00.000'.

INSERT INTO cusLedgerCode
 (LastDate
DoctorFacilityId
ListName
Ledger
)
SELECT LastDate
DoctorFacilityId
ListName
Ledger FROM DoctorFacility
WHERE cast(LastDate as datetime) BETWEEN '2006-12-31 00:00:00.000' and '2007-12-31 00:00:00.000'

Author

Commented:
aneeshattingal -
I forgot to add in a condition on my query to only pull in specfic DoctorFacility.Type ('s). When I ran your query in my test db it updated all of them, which was my error. Is it possible to delete out all the values in my table and add back in my new condition and re-try this?
I need it to only pull in the DoctorFacility types (1,2,5,6,7). Can i truncate the table or is their another method to use?
Database Consultant
Top Expert 2009
Commented:
better truncate the table, its really hard to delete


insert into cusLedgerCode
SELECT a.dt, DoctorFacilityId ,ListName, Ledger
FROM DoctorFacility  d
CROSS JOIN  (SELECT '2007-12-31' as dt UNION SELECT '2006-12-31' ) a
 WHERE NOT EXISTS (SELECT 1 FROM cusLedgerCode c WHERE  c.DoctorFacilityId = d.DoctorFacilityId )
and d.DoctorFacilitytype in  (1,2,5,6,7)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial