How to skip same duplicate key data when copy table

oracle sqlbulkcopy sql-insert subquery

Question

I have a table1 with the key "X, XX" that looks like this:

x,y,xx,yy,xxx,yyy

I created a new table2 with the following key: code:

code,name,xx,yy,xxx,yyy

I now want to replicate all of the data from table 1 to table 2, skipping any instances of the same code.

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

To copy all data, I used the code below, but I received an error.00001. 00000 - "unique constraint (%s.%s) violated" X is duplicated in table 1, however I'm not sure how to avoid redundant X data; could you please assist me?

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

I believe that is incorrect, having tested it.

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

Attempt 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

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

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

Popular Answer

Try this with a 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 inserts will fail due to duplication, but the majority should be.

A different choice without a 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 reality, they copy to a table inside the same database in the example code seen in ...here....
sql sqlbulkcopy sql-server
asked by Joe Stefanelli

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

You're setting the table. Rows. It seems that Add(row) and a New SqlBulkCopy() are equivalent.
asp.net sqlbulkcopy vb.net
asked by servermanfail

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

Hello, for some reason the code below is not working:...I upload a pipe-delimited file using the fileupload control of an Asp.net web application, and I use oledb to populate the dataset. The data is then copied from the data database to a SQL table that I have created using sql bulk copy....protected void btnUpload_Click(object sender, EventArgs e...
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 apparent what you're attempting to do with the data from your Excel import in these two tables or how and where you're trying to save it....groupID... occurs in.......In essence, you are correct; bulk inserting into many tables is not possible. So, here's what I'd say:...1) Insert data in bulk from Excel into a...Staging... today's 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#?

Use a ...connected server... in T-SQL to carry it out....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...
c# entity-framework sqlbulkcopy
asked by Mitch

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

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

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

I suppose you could utilize...INSERT INTO SELECT... the phrase "copy data from"...table1... to ...table2... ....INSERT INTO table2 (col1,col2 ...) SELECT col1,col2... FROM table1; ...It may be found here:...http://www.w3schools.com/sql/sql_insert_into_select.asp... Your SQL may sound like this:...INSERT INTO generic (tableId, ColumName,rowNo,CellD...
sql sqlbulkcopy sql-server-2008
asked by naota

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

Not at all, no....However, as a coder, you may validate data before adding. You know, it's really not that difficult. Additionally, using regular objects and your own implementation of the necessary interface, you may copy data into the SqlBulkDCopy instance without the requirement for a HEAVY data table at all.
c# datatable sql sqlbulkcopy
asked by TomTom

How to insert excel data into foreign key table

Your first data may be bulk-inserted into a staging table. Then, populate each lookup table with your data using a sequence of commands....When rows rely on foreign key values, how should BULK INSERT be used?...SQL Server Integration Services is an additional tool in the MSSQL platform and a more capable ETL tool. You're describing an instance in w...
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 must examine the data in Access to identify and correct any incorrect column values, or you must deal with them in your TSQL after the export. Replace the temporary table with a real table, change the datetime datatype to string, export your data, and then query the database to locate the incorrect values so you know what sort of information to...
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