Solved

Help With Inserting New Records Only

Posted on 2013-12-04
5
192 Views
Last Modified: 2013-12-05
I have raw data imported into a table from a flat file.

I want to take that data and insert records only that are new based using two columns as my references. If those two aren't new then the record is not updated.

Any ideas on what method is the best/easiest for this?
0
Comment
Question by:sonofstimpy1
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 39695417
Create a temp table and import the records first into it
You can then use NOT EXISTS

insert into ABC
select *
from ##temptable A
where not exists(select 1 from ABC B where A.someid = B.someid)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39695423
>only that are new based using two columns as my references
What is your definition of 'new'?

If this is a staging table, typically the table is deleted before the import, hence all rows are 'new'.

If this is a production table where there are existing rows that need to stay, a common method would be to have 'auditing' columns such as CreatedBy, CreatedDate, where the CreatedDate has a GETDATE() inserted into it, such that identifying rows 'just imported' is as easy as getting the latest CreatedDate.

If the definition of 'new' is something else you'll need to spell it out for us.
0
 

Author Comment

by:sonofstimpy1
ID: 39696523
Just using generic TABLE1 and TABLE2

They're both the same data with the same data fileds
Let's say TABLE 1 adds 5 new records.
And I really don't want to drop TABLE 2.
I'm looking for something that will see that there are 5 new records and insert those records if 2 of say 10 fields are unique (call them memberid and date).

Is this making more sense?
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 39698143
insert into TABLE1
select *
from TABLE2 A
where not exists(select 1 from TABLE1 B where A.memberid = B.memberid and A.date = B.date)
0
 

Author Closing Comment

by:sonofstimpy1
ID: 39699557
That's exactly what I was looking for.

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now