An Iteration with renaming and avoiding duplicates
-
Want to insert all the Rows of a table in a new table with a renaming activity. Also we have some exceptions that we shouldn't move them. Renaming Act : Change the char '_' into ' ' Exceptions : Items that if we rename they would be duplicates in the new table. So all the items there would be unique and renamed version of our previous table. - Here we don't have any integer ID's, - The database is SQL Server CE --- * Our Tables : Product, tblNew * Each with same columns : "Product", "col1", "col2" Want to move the records to "tblNew" , our key column that we don't want duplicates is "product"
-
Want to insert all the Rows of a table in a new table with a renaming activity. Also we have some exceptions that we shouldn't move them. Renaming Act : Change the char '_' into ' ' Exceptions : Items that if we rename they would be duplicates in the new table. So all the items there would be unique and renamed version of our previous table. - Here we don't have any integer ID's, - The database is SQL Server CE --- * Our Tables : Product, tblNew * Each with same columns : "Product", "col1", "col2" Want to move the records to "tblNew" , our key column that we don't want duplicates is "product"
so your purpose is to move rows from one table to another but without duplicates Your Unique key is Product Ok Now My question is If duplicate found depending on Product column then which row should replicate Please provide the logic...
-
Want to insert all the Rows of a table in a new table with a renaming activity. Also we have some exceptions that we shouldn't move them. Renaming Act : Change the char '_' into ' ' Exceptions : Items that if we rename they would be duplicates in the new table. So all the items there would be unique and renamed version of our previous table. - Here we don't have any integer ID's, - The database is SQL Server CE --- * Our Tables : Product, tblNew * Each with same columns : "Product", "col1", "col2" Want to move the records to "tblNew" , our key column that we don't want duplicates is "product"
Use a cursor/while loop, probably easier with a cursor. get the field to be renamed into a variable test if it exists in the target insert or ignore base on the test
Never underestimate the power of human stupidity RAH
-
so your purpose is to move rows from one table to another but without duplicates Your Unique key is Product Ok Now My question is If duplicate found depending on Product column then which row should replicate Please provide the logic...
seems you didn't get the question, We need to move rows without any duplication. But From the beginning there is no duplicates, Right ? During our movement, we want to change a char '_' into ' ', and this will cause some duplications , ok ? so we want to avoid this, How ? when moving the rows we don't move 'product's which will be a future duplicate after this renaming : Imagine in our products list we have : Baby_Doll ( a future duplicate ) and Baby Doll, we have lots of other Baby_Brush, Baby_toy1 , Baby_toy2 , ... ( These are not future duplicates )
-
Use a cursor/while loop, probably easier with a cursor. get the field to be renamed into a variable test if it exists in the target insert or ignore base on the test
Never underestimate the power of human stupidity RAH
@Mycroft Holmes, seems to be a productive way, but could you please give the 5 lines of code you are talking about, please also re-consider it's SQL CE, cause it lacks near everything ! Also for clarifying more of this situation please look at my previous reply on this thread,
-
@Mycroft Holmes, seems to be a productive way, but could you please give the 5 lines of code you are talking about, please also re-consider it's SQL CE, cause it lacks near everything ! Also for clarifying more of this situation please look at my previous reply on this thread,
SkyRunner wrote:
but could you please give the 5 lines of code you are talking about,
Nope, you write the code and when you get stuck comeback and ask for help. As it is sql ce you will probably have to do this in code, the same logic applies. Get the original table into a list<> get and empty list<> loop through the data inserting the unique records into the new list<> write the new list<> back to the new table in the database.
Never underestimate the power of human stupidity RAH
-
SkyRunner wrote:
but could you please give the 5 lines of code you are talking about,
Nope, you write the code and when you get stuck comeback and ask for help. As it is sql ce you will probably have to do this in code, the same logic applies. Get the original table into a list<> get and empty list<> loop through the data inserting the unique records into the new list<> write the new list<> back to the new table in the database.
Never underestimate the power of human stupidity RAH
:D here I rote tens on thousands lines of code which are working, about this subject I asked I'm stuck that I've asked, I was a bit away from dealing with pure CQL scripts, and found SQL CE a real big hassle . Here I rote lots of scripts with similar errors, I can't do a select insert query and so on ! may I'm wrong on some pieces in each code I tried, Here I was working on this :
INSERT INTO Table1(Column1)
SELECT REPLACE(OriginalColumn, '_', '')
FROM Table2 t2
LEFT OUTER JOIN Table1 t1 ON t1.Column1 = REPLACE(OriginalColumn, '_', '')
WHERE t1.Column1 IS NULLbut still couldn't make it to meet my Tables and columns, What about this, or the situation I've explained ?
-
SkyRunner wrote:
but could you please give the 5 lines of code you are talking about,
Nope, you write the code and when you get stuck comeback and ask for help. As it is sql ce you will probably have to do this in code, the same logic applies. Get the original table into a list<> get and empty list<> loop through the data inserting the unique records into the new list<> write the new list<> back to the new table in the database.
Never underestimate the power of human stupidity RAH
-
Want to insert all the Rows of a table in a new table with a renaming activity. Also we have some exceptions that we shouldn't move them. Renaming Act : Change the char '_' into ' ' Exceptions : Items that if we rename they would be duplicates in the new table. So all the items there would be unique and renamed version of our previous table. - Here we don't have any integer ID's, - The database is SQL Server CE --- * Our Tables : Product, tblNew * Each with same columns : "Product", "col1", "col2" Want to move the records to "tblNew" , our key column that we don't want duplicates is "product"
First, find out if this is really a big issue:
SELECT
REPLACE([ProductName],'_','') AS NewName,
COUNT(*)
FROM
[Product]
GROUP BY
REPLACE([ProductName],'_','')
HAVING
COUNT(*) > 1You may find that you have only a few rows that you can manually update/delete prior to the move. Next - what are you going to do with the new duplicates? Are you not bringing them over or do you want to generate a unique name for them or are you going to do some fancy merge of the other columns/linked tables?
-
First, find out if this is really a big issue:
SELECT
REPLACE([ProductName],'_','') AS NewName,
COUNT(*)
FROM
[Product]
GROUP BY
REPLACE([ProductName],'_','')
HAVING
COUNT(*) > 1You may find that you have only a few rows that you can manually update/delete prior to the move. Next - what are you going to do with the new duplicates? Are you not bringing them over or do you want to generate a unique name for them or are you going to do some fancy merge of the other columns/linked tables?
we are making/filling a new table and after filling that with the desired table, the previous table is a subject to delete, so will not do anything regarding the duplicates ( They stay to be deleted ) Do you know how can I use Select / Insert here to accomplish this task and bring all the columns data with a condition checking about preventing the insertion of future-probable-duplicates ? There are about 180,000 rows which we will not move about 10,000 of it, The database is generated by code, and everything was based on decision and observation.
-
we are making/filling a new table and after filling that with the desired table, the previous table is a subject to delete, so will not do anything regarding the duplicates ( They stay to be deleted ) Do you know how can I use Select / Insert here to accomplish this task and bring all the columns data with a condition checking about preventing the insertion of future-probable-duplicates ? There are about 180,000 rows which we will not move about 10,000 of it, The database is generated by code, and everything was based on decision and observation.
Something like this should work:
INSERT INTO [NewProductTable]
(
[NewProductName],
[NewCol1],
[NewCol2]
)
SELECT
REPLACE([ProductName],'_',' '),
[Col1],
[Col2]
FROM
[Product]
WHERE
(REPLACE([ProductName],'_',' ') = ProductName) OR
(REPLACE([ProductName],'_',' ') NOT IN (SELECT [ProductName] FROM [Product]))I want you to understand that any child records (think foreign key) based off the ignored rows will be orphaned in the new structure.
-
Something like this should work:
INSERT INTO [NewProductTable]
(
[NewProductName],
[NewCol1],
[NewCol2]
)
SELECT
REPLACE([ProductName],'_',' '),
[Col1],
[Col2]
FROM
[Product]
WHERE
(REPLACE([ProductName],'_',' ') = ProductName) OR
(REPLACE([ProductName],'_',' ') NOT IN (SELECT [ProductName] FROM [Product]))I want you to understand that any child records (think foreign key) based off the ignored rows will be orphaned in the new structure.
I think the logic should be almost right, except a piece which I have a little doubt in the 'Where' condition in addition to my never-ending doubt on SQL CE, let me try it and give you the feedback of your code, Thanks @Michael Potter, your help is really appreciated, that is the last step regarding the database and I hope it could help on the objective desired.