Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 copying data from one table to another

Author  Topic 

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 15:18:44
I know this coding doesn't work, but does anyone know how to do something like this:


 IF NOT EXISTS(SELECT * from tblCapRec) INSERT INTO tblCapRec (*) VALUES (*) From tblCapRecBeforeChanges


I need to copy data from one table and insert it into another table, but I need to check to see if that data exists already. You help is greatly appreciated. Thanks!

Brenda

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-09-20 : 15:22:25
Use INSERT INTO with a SELECT statement. The SELECT statement would use LEFT OUTER JOIN. Only insert the rows where the data is NULL.

Tara
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 15:52:25
Hi Tara

Do I have to list each individual value in the tables or can I just use (*)? The tables have the exact same structure.

Brenda
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-09-20 : 15:55:06
The only time that * should be used is with IF EXISTS or IF NOT EXISTS. Otherwise, you receive a performance hit.

Tara
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 15:57:12
But how can I join two tables if I don't have any data in one of them?

Brenda
Go to Top of Page

influent
Constraint Violating Yak Guru

367 Posts
Posted - 2004-09-20 : 16:00:30
Outer joins will work, inner joins will not.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-09-20 : 16:04:41
You don't have to have data in them to join them together.

Tara
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 16:24:59
Am I doing this join right? 

        INSERT INTO tblCapRecBeforeChanges (CaseNumber,PartNumber,Status,LastName,FirstName) SELECT t1.CaseNumber,1,1,t1.Lastname,t1.FirstName FROM tblBetween600_1500 as t1 INNER JOIN tblAccurint_Between600_1500 as t2 ON t1.CaseNumber = t2.acctno


Or what kind of join should it be?

Brenda
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-09-20 : 16:26:38
It should be a LEFT OUTER JOIN and you'll need a WHERE clause. If I weren't so sick with a cold today, I could think clearly and provide you a solution. I'm in a fog today.

Tara
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 16:50:45
Why is it an LEFT OUTER JOIN? I am taking information from two different tables and inserting it into a third table. You must be VERY dedicated to be on here while you're sick. Thanks!

Brenda
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 17:03:17
Can you tell me why this isn't working:

join = "INSERT INTO tblCapRecBeforeChanges (CaseNumber," & _
        "PartNumber,Status,LastName,FirstName,SSN,OtherSSN,OtherNotes,InGDB," & _
        "DocsOrdered, DocsHere, AppHere, Ubum, Suspended, Trace, SentToHUD, SentToTSI, SentToCredit, " & _
        "SpanishCase, MailAddress, MailCity, MailState, MailZipCode, CareOf, HomePhone, OtherPhone, " & _
        "SpousePhone, PropAddress, PropCity, PropState, PropZipCode, Updated, MailKey, MailedFirst, MortgageAmt, " & _
        "PaidUpFront, EndorseDate, Term, MatureDate, EncumDate, HoldingMor, ServingMor, RcvdFromOld, RcvdFromRegular, " & _
        "RcvdFromRtn,RcvdFrom2s,RefundAmt) SELECT t1.CaseNumber,1,1,t1.Lastname,t1.FirstName," & _
        "t2.akas_ssn_1, t2.akas_ssn_2, t2.subj_first_1 + t2.subj_middle_1 + t2.subj_last_1 + t2.subj_suffix_1, " & _
        "0,0,0,0,0,0,0,0,0,0,0,t2.subj_address_1,t2.subj_city_1,t2.subj_state_1,t2.subj_zipcode_1," & _
        "t1.CareOf, t2.subj_phone10_1, subj_phone10_2, t2.subj_phone10_3, t1.PropAddress, t1.PropCity, " & _
        "t1.PropState, t1.PropZipCode, GETDATE(), 1, GETDATE(), t1.MortAmount, t1.PaidUpFront, " & _
        "t1.EndorseMonth+'/'+t1.EndorseDay+'/'+t1.EndorseYear,t1.Term," & _
        "t1.MaturityMonth+'/'+t1.MaturityDay+'/'+t1.MaturityYear,t1.EncumMonth+'/'+t1.EncumDay+'/'+t1.EncumYear," & _
        "t1.HoldingMor,t1.ServingMor,0,0,0,0,t1.RefundAmount" & _
        "FROM tblAbove1500 t1 OUTER LEFT JOIN tblAccurint_Above1500 t2 ON t1.CaseNumber = t2.acctno"


It says it doesn't like the 't1'

Brenda
Go to Top of Page

influent
Constraint Violating Yak Guru

367 Posts
Posted - 2004-09-20 : 17:18:35
Are you testing this in Query Analyzer? Do you at least get an error number, if nothing else?
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 17:24:12
When I run it in QA, it says, "Incorrect syntax near the keyword 'OUTER'."

Brenda
Go to Top of Page

brendalisalowe
Constraint Violating Yak Guru

269 Posts
Posted - 2004-09-20 : 17:27:15
I just took out the OUTER and it ran. But is it getting all the proper data?

Brenda
Go to Top of Page

influent
Constraint Violating Yak Guru

367 Posts
Posted - 2004-09-20 : 17:39:46
I think you'll have to run it as a SELECT statement instead of an INSERT INTO in QA in order to see the data.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-09-21 : 03:04:33
you can try...

insert into tblCapRecBeforeChanges
select <your fields here> from tblCapRec as t1
join tblCapRecBeforeChanges as t2
on t1.casenumber<>t2.acctno

--edit
this will insert into tblcaprecbeforechanges whatever was not found from tblcaprecbeforechanges but are in tblcaprec
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site