Database

Re: An Iteration with renaming and avoiding duplicates

16-Jul-12 8:37

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"

Suvabrata Roy16-Jul-12 8:55

Suvabrata Roy

16-Jul-12 8:55

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...

Re: An Iteration with renaming and avoiding duplicates

SkyRunner17-Jul-12 2:36

Re: An Iteration with renaming and avoiding duplicates

17-Jul-12 2:36

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 )

Mycroft Holmes16-Jul-12 12:58

Re: An Iteration with renaming and avoiding duplicates

16-Jul-12 12:58

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

SkyRunner17-Jul-12 2:39

Re: An Iteration with renaming and avoiding duplicates

17-Jul-12 2:39

@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 Holmes17-Jul-12 2:59

Re: An Iteration with renaming and avoiding duplicates

17-Jul-12 2:59

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

SkyRunner17-Jul-12 3:08

Re: An Iteration with renaming and avoiding duplicates

17-Jul-12 3:08

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 :

SQL

INSERT INTO Table1(Column1)
    SELECT  REPLACE(OriginalColumn, '_', '')
      FROM  Table2 t2
            LEFT OUTER JOIN Table1 t1 ON t1.Column1 = REPLACE(OriginalColumn, '_', '')
     WHERE  t1.Column1 IS NULL

but still couldn't make it to meet my Tables and columns,
What about this, or the situation I've explained ?

SkyRunner17-Jul-12 3:44

Re: An Iteration with renaming and avoiding duplicates

17-Jul-12 3:44

Also forgot to mention, The process was really heavy, now just lloking into sql script to do the final job and do not want to do it via code.

Michael Potter17-Jul-12 3:52

Michael Potter

17-Jul-12 3:52

First, find out if this is really a big issue:

SQL

SELECT
     REPLACE([ProductName],'_','') AS NewName,
     COUNT(*)
FROM
     [Product] 
GROUP BY
     REPLACE([ProductName],'_','')
HAVING
     COUNT(*) > 1

You 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?

Re: An Iteration with renaming and avoiding duplicates

SkyRunner17-Jul-12 4:11

Re: An Iteration with renaming and avoiding duplicates

17-Jul-12 4:11

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.

Michael Potter17-Jul-12 4:51

Michael Potter

17-Jul-12 4:51

Something like this should work:

SQL

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.

Re: An Iteration with renaming and avoiding duplicates

SkyRunner17-Jul-12 23:24

Incorrect Reporting Service instance name during SQL server 2005 SP3 installation

17-Jul-12 23:24

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.

Vipul Mehta15-Jul-12 22:55

Vipul Mehta

15-Jul-12 22:55

Hi,

Some time back my machine (Win XP) got renamed from M4B-11-720-GXP to M4B-06-425-GXP. While inatalling MS SQL Server 2005 SP3, the installer still shows me the old Reporting Service instance name - M4B-11-720-GXP\ReportServer. As a result installation fails on the authentication windows since there is no such instance present on my machine. Is there any way to get rid of this issue? From where does it load the old instance name? Can we change that? Also is there any way to unselect the "Reporting Services" option from the previous screen?

Your prompt response on this would be highly appreciated.

Thanks in advance.

Regards,
Vipul Mehta

modified 16-Jul-12 5:04am.

Dinesh kumar tomer15-Jul-12 18:44

Dinesh kumar tomer

15-Jul-12 18:44

i am bigineer plese give me the login page code

Re: login page

ManishaSCode15-Jul-12 19:25

ManishaSCode

15-Jul-12 19:25

Hi Dinesh,

First please let us know the programming language you are using for login page. second the design of login page.

Thanks

https://sites.google.com/site/freelancermanisha/

Re: login page

Paul Conrad16-Jul-12 10:55

Paul Conrad

16-Jul-12 10:55

It doesn't work like that around here. We don't just give out code. You should really try working on your own code and when you run into any problems with it, then ask.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Can a SQL login have more than one user

yousefshokati14-Jul-12 19:53

yousefshokati

14-Jul-12 19:53

Hello .
I know that a SQL login is used to log into the server and SQL user is used to log to database but i want to know that is their a 1 to 1 correspondence between a SQL Login and SQL user or a login can have more than one SQL user that can be mapped to it.
Thanks

Mycroft Holmes14-Jul-12 20:27

14-Jul-12 20:27

yousefshokati wrote:
user that can be mapped to it.

How are you mapping a user to a login? As a SQL login consists of a user name and a password how are going to know if a user has not shared that information.

We use integrated security for the developers, applications has their own sql identities and pass in the workstation id and no other connections are allowed!

Never underestimate the power of human stupidity
RAH

yousefshokati14-Jul-12 20:50

yousefshokati

14-Jul-12 20:50

I am asking only that
A SQL User (database -level security) must map to a login . It is very obvious .Now I am only asking you that can a login have more that one database-level user ?
We know that a Login and a user are related with each other by SID Ok? Now i want to know that is it possible for a login , that can have nmore than one user ?

Mycroft Holmes14-Jul-12 22:28

14-Jul-12 22:28

Ah, more context...

Yes a user can have many connections, an example would be using SSMS each query window creates it's own connection using your credentials. If, in SSMS, you look at the activity monitor you will find multiple SID per login.

You terminology is difficult, what do you mean by a User. A database is only interested in a connection, each connection has credentials. A set of credentials (presumably your User) may login multiple times.

Never underestimate the power of human stupidity
RAH

ManishaSCode15-Jul-12 3:34

ManishaSCode

15-Jul-12 3:34

Hi,

Principals are entities that can request SQL Server resources.
SQL Server Login is SQL Server-level principal
A database user is a principal at the database level.

One Login can be associated with many users (one per database).

Thanks

https://sites.google.com/site/freelancermanisha/

Jan Steyn16-Jul-12 0:34

Jan Steyn

16-Jul-12 0:34