How to skip same duplicate key data when copy table

oracle sqlbulkcopy sql-insert subquery

Question

I have a table1(key is X, XX) looks like below:

x,y,xx,yy,xxx,yyy

I created a new table2 (key is code) as below:

code,name,xx,yy,xxx,yyy

Now I wan to copy all data in table1 to table2, and if met same code skip it, 

x -> code
Y -> name
xx-> xx
yy -> yy
xxx -> xxx
yyy -> yyy

I used code as below to copy all data, I got error 00001. 00000 - "unique constraint (%s.%s) violated" because there is duplicated x in table1, but I am not sure how to skip duplicated X data, can you help me?

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT x, y, xx, yy, xxx, yyy FROM table1

I tried this, I think it is not correct.

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT DISTINCT x, y, xx, yy, xxx, yyy FROM table1
1
1
6/28/2017 5:34:49 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

try this:

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT DISTINCT x, y, xx, yy, xxx, yyy FROM table1
where x not in (select code from table2)

OR

use hint /*+ ignore_row_on_dupkey_index(table2, table2_index) */

1
6/28/2017 10:41:32 AM

Popular Answer

You can try with cursor : 

DECLARE
Cursor c1 is select code, name, xx, yy, xxx, yyy from table1;

for insertValue in c1
loop

insert into table2(code, name, xx, yy, xxx, yyy) values (insertValue.code, insertValue.name,insertValue.xx ... );

end loop;

Few insert will fail because of duplication , but rest should be inserted.

Or other option without cursor is : 

BEGIN
 FOR insertValue IN (
 select code, name, xx, yy, xxx, yyy from table1 )
LOOP
insert into table2(code, name, xx, yy, xxx, yyy) values (insertValue.code, 
insertValue.name,insertValue.xx ... );  
END LOOP;
END;
/
pritesh agrawal

Related Questions

Is it possible to transfer data across servers using sql bulk copy?

Yes. In fact, in the demo code shown ...here..., they copy to a table within the same database.
sql sqlbulkcopy sql-server
asked by Joe Stefanelli

When each record is added to the database, bulk copy duplicates it.

Your doing a table.Rows.Add(row) and a New SqlBulkCopy() They seem to be the same?
asp.net sqlbulkcopy vb.net
asked by servermanfail

The bulk copy from the data table in sql does not upload.

Hello I have the code below that for some reason is not working:...I use oledb to fill a dataset from a pipe delimited file that is uploaded through a fileupload control on an asp.net web application. I then take the data table and then use sql bulk copy to copy the data to a table i have setup in sql. ...protected void btnUpload_Click(object sende...
asp.net c# sql sqlbulkcopy
asked by Jeff

In SQL Server 2005, how can I bulk transfer comparable data to two tables?

It's not quite clear what ...exactly... and how exactly you're trying to store the data from your Excel import into these two tables, and where that ...groupID... comes from.......Basically - you're right - you cannot bulk insert into multiple tables. So my take would be:...1) Do the bulk insert from Excel into a ...Staging... table as today...2) T...
sql sqlbulkcopy sql-server-2005
asked by marc_s

How to copy and validate data from one table (all varchar) to another (typed) in C#?

Do it in T-SQL with a ...linked server.... ...I.e.: ...--begin a transaction to wrap validation and load BEGIN TRAN --Validate that no tickets are set to closed without a completion date SELECT * FROM bigTableOnLocalServer with (TABLOCKX) -- prevent new rows WHERE ticketState = '1' /* ticket closed */ and CompletionDate = 'open' --if validatio...
c# entity-framework sqlbulkcopy
asked by Mitch

Bulk copy of SQL data from table on disk to an in-memory table

create a class.....public class MemoryObject { public Int64 Id {get; set;} public string OtherProperty {get; set;} } ...then, use a SQLDataReader......public List<MemoryObject> GetMemoryObjects() { using (SqlConnection connection = new SqlConnection("...")) { string sql = "query"; SqlCommand command = new Sql...
c# database sql sqlbulkcopy sql-server
asked by christiandev

move data from one table to other table with different table structure

I guess you can use ...INSERT INTO SELECT... statement to copy data from ...table1... to ...table2.......INSERT INTO table2 (col1,col2 ...) SELECT col1,col2... FROM table1; ...The Document is here:...http://www.w3schools.com/sql/sql_insert_into_select.asp... Your SQL could go something like this:...INSERT INTO generic (tableId, ColumName,rowNo,Cell...
sql sqlbulkcopy sql-server-2008
asked by naota

Validating data in my Data Table before attempting to write to the database with SQLBulkCopy

No, there is not....But you can as a programmer do validation before inserting. Not exactly that hard, you know. And there is no need to have a HEAVY data table at all - use normal objects and hammer them into the SqlBulkDCopy instance using your own implementation of the required interface ;)
c# datatable sql sqlbulkcopy
asked by TomTom

How to insert excel data into foreign key table

You can bulk insert your initial data into a staging table. Then use a series of statements to create your data in each lookup table....How to use BULK INSERT when rows depend on foreign keys values?...Another tool in the MSSQL platform is SQL Server Integration Services, a more robust ETL tool. You're describing a scenario where the Lookup Task re...
asp.net c# oledb sqlbulkcopy sql-server-2008
asked by JJS

Missing Data after insert into my end sql table using Sql Bulk Copy

you need to look at the data in Access, and find/fix those bad column values, or handle them in your TSQL after the export. One option to debug - replace temp table with real table, replace datetime datatype with string, do your export, then query the table to find the bad values so you know what kind of data to look for. After that, you can swit...
c# console-application datatable sqlbulkcopy sql-server-2008
asked by Greg

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow