Click here to Skip to main content
15,942,934 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I have SQL server Database table with more than 30 million records, what is the best way to insert unique values to this table

Any suggestions.

What I have tried:

I tried:

if not exists (select * from MyTable where Name = @Name and ... )
begin
insert into MyTable (Name,...) values (@Name,...)
end

But that is taking lots of time to execute

I created an index to every column im checking if its exist
Posted
Updated 15-Jul-16 3:19am
Comments
[no name] 15-Jul-16 6:59am    
Why not _one_ unique index over the columns you have in the "where" clause?
Hidhoud1991 15-Jul-16 7:37am    
they will exceed the 900 byte allowed for index size
[no name] 15-Jul-16 7:46am    
I understand. Maybe this will give you an idea, but I assume you know it allready: Living with SQL’s 900 Byte Index Key Length Limit – Bart Duncan's SQL Weblog[^]

1 solution

I think You need to insert one table data to another table.
SQL
INSERT INTO destTable
SELECT Field1,Field2,Field3,... 
FROM srcTable
WHERE NOT EXISTS(SELECT * 
                 FROM destTable 
                 WHERE (srcTable.Field1=destTable.Field1 and
                       SrcTable.Field2=DestTable.Field2...etc.)
                 )




SQL
insert into test.t2(name2) 
select distinct name1 from test.t1 t1 where NOT EXISTS(select name2 from test.t2 t2 where t1.name1=t2.name2);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900