Checking for Duplicate Records First

I am updating a tempory database table with a file.

Once i have read it into the tempory table, i want to update the master table, but only for new records

IE, each record has an account code, which is unique. The first time the master table is updated there will be no problems, as there won't be any data in it already. All future updates, will require that they check there isn't an existing record with the same account code.

Basically

Update Table1 from Table2 where table1.accountcode not = table2.accountcode

i don't know if that makes sense.

Can anyone help me please.

Answer this question

Checking for Duplicate Records First

SusanBaroody
A slick solution would be use of stored procedures where you do the actual updating. But of course, it depends on the wider view on your problem.
Thierry Lam

Here is an example i created for you. It is not exactly what you are using because i did not understood you well:

update dbo.tmp
set [Id] = 1,
[text] = 'test'
where 1 not in(select [Id] from dbo.tmp)

If you want further help, try explaining the problam more clearly.

Anyway,

I hope this helps,

Regards,
Tutnik

I created a sample script for you, let me know if that helps:

Create Table #tbl1
(
Val int
)

Create Table #tbl2
(
Val int
)

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

insert #tbl1
select 1

insert #tbl1
select 2

insert #tbl1
select 3

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

insert #tbl2
select 1

insert #tbl2
select 2

insert #tbl2
select 3

insert #tbl2
select 4

insert #tbl2
select 5

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

insert #tbl1

Select Val from #tbl2
where Val not in (select Val from #tbl1)

select * from #tbl1

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

drop Table #tbl1
drop Table #tbl2

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

Regards,
Kode Hunt
No, not really. I don't quite follow all your code until this section

insert #tbl1

Select Val from #tbl2
where Val not in (select Val from #tbl1)

select * from #tbl1

which is all i want to do.

All im looking for is the structure of an SQL statement that will

Insert into Table1 (values....) whereTable2(values) not in Table1

No matter what i try, i keep getting an "insert into syntax error"

It must be a faily standard thing for people to want to do with a database, and i can't find an single useful thing on the web to help me.
CoolCoolMan
Ok.

Table1 has records

a,b,c,d,e,f

Table2 has records

a,.b,c,d,e,f,g,h,i

I want to update table 1 with the records from table2. I do NOT want to update any existing records in table1. I just want to add records g,h,i.

The program will NOT be able to determine what records are new, hence i need to link it to a specific index column and check that the values for that column in table2 and not already in table1.

If they are, then those rows are ignored. If they are not, then those rows are added.

Table2 is just a tempory table for storing a file that the end-user will select. As such, this table will always have EVERY record in it, not just new ones, hence the checking before updating the permanent table (Table1)

Sorry for the previous explanation.
Checking for Duplicate Records First

Answer this question