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.
Checking for Duplicate Records First
Deadman
Sculli
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,
rvUser
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.
jbud55
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.
N.S.
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,