October 28, 2008 at 1:14 pm
I have a table called ptAppointments and a table called ptSchedule.
We want all records that are in ptSchedule where the ScheduleID is
not in ptAppointments ID column to be inserted.
ptSchedule.ScheduleID column is type int and ptAppointments.ID column is type varchar(20)
{don't ask why}
Can anyone show me a quick way to do that?
October 28, 2008 at 1:54 pm
Depends on what you mean by "quick":
Insert into ptApointment(...)
Select ...
From ptSchedule s
Where ScheduleID NOT IN(Select Cast(a.ID as int) from ptApointment a
Where ISNUMERIC(a.ID))
I doubt that this will run very fast...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 28, 2008 at 2:02 pm
This is the general way to do this. Because you did not supply the schemas or clearly define what column in ptAppointments represents ScheduleID I used generic language. If it is ID you may have issues with the implicit conversion. If you do get a conversion error you can use this in the join, A.id Not Like '%[^0-9], which will eliminate non-numerics.
Insert Into ptAppointments
(
column list
)
Select
column list
From
dbo.ptSchedule S Left Join
dbo.ptAppointments A On
S.pk_key = A.fk_key
Where
A.ID is null
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2008 at 2:03 pm
rbarryyoung (10/28/2008)
Depends on what you mean by "quick":
Insert into ptApointment(...)
Select ...
From ptSchedule s
Where ScheduleID NOT IN(Select Cast(a.ID as int) from ptApointment a
Where ISNUMERIC(a.ID))
I doubt that this will run very fast...
or
insert into ptAppointment(...)
select ...
from
ptSchedule s
left outer join ptAppointment a
(s.ScheduleID = cast(a.ID as int))
where
a.ID is null;
Still may not run very fast.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply