duplicates

How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.See if this helps:
http://support.microsoft.com/defaul...kb;en-us;139444
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.|||Please refer to www.aspfaq.com/5006 and provide sufficient information for
others to better understand you problem.
Deletes are applicable to a set of rows, not individual columns. If the
entire row is duplicated, then use the link posted by Vyas & make sure you
explicitly declare a primary key to avoid further duplication. If only the
sytemname is duplicated, they you will have to decide which values of memory
and/or cpu should be preserved and which ones should be deleted. Generally,
one can apply an extrema aggregate like MIN or MAX like:
SELECT *
FROM tbl t1
WHERE t1.Memory = ( SELECT MAX( t2.memory ) FROM tbl t2
WHERE t2.system = t1.system )
You can convert this into a delete like:
DELETE FROM tbl
WHERE Memory = ( SELECT MAX( t1.memory ) FROM tbl t1
WHERE tbl.system = t1.system )
If the duplicated values are determined by memory and/or cpu, you can use
WHERE Memory + '!' + cpu = ( SELECT MAX( t1.Memory + '!' + t1.cpu ) ...
Another way of writing it would be
DELETE FROM tbl
WHERE EXISTS ( SELECT * FROM tbl t1
WHERE t1.Memory > tbl.Memory
OR t1.cpu > tbl.cpu
OR ... )
Anith|||Of course, once you have duplicates purged, you should implement a unique
key constraint to prevent this from occurring again. But to answer your
question, let's say you have a table called Accounts and the column
combination tran_type should be unique. The following query will list all
records that violate the non-unique rule. We are basically joining back to a
group by sub-query having count(*) > 1. You can modify this as needed to fit
your situation. Once done, you can better profile the records to determine
which should be deleted. For example, do you want to keep the first entered
or last entered, or perhaps duplicates entered during a specific time frame
should be deleted.
select
Accounts.*
from
Accounts
join
(
select
account,
tran_type,
count(*) as cnt
from
Accounts
group by
account,
tran_type
having
count(*) > 1
) as x
on x.account= Accounts.account and
x.tran_type= Accounts.tran_type
order by
NSA.account,
NSA.tran_type
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
> How do I delete duplicate entries?
> For example, lets says I have the following table format:
> SystemName, Memory, CPU
> I would like to delete any duplicates that are in SystemName.
>
>

Duplicate xReference Fields in 1 query ?

HI

Its been a while since i have posted here, but this one has stumped me. maybe i am just super hungry right now and missing something obvious... but i can get this one.

i would search the forums for the answer, but not even sure what to search for... not even sure if this (left joins) is the way to accomplish what i want.

Using MS-SQL database as the back end.

I have 3 data tables.
Table 1 is a cross reference table.
Table 2 is the primary data (group data)
Table 3 is the secondary data (users data) (multiple users in a group)

table 1 provides the text that matches the respective status from both the 2nd and 3rd tables.

xRefTable
MemberStatus MemberStatusText
---- ------
0 Non Member
1 Referral
2 Resident
3 Non Resident

PrimaryUserTable
GroupID GroupStatus
-- ----
1 0
2 3
3 1
4 2

SecondaryDataTable
MemberID GroupID MemberStatus
--- --- ----
1 1 0
2 1 0
3 1 0
4 1 0
5 2 3
6 2 3
7 3 1
8 3 1
9 4 2
10 4 1
11 4 1
12 4 3

The ultimate results i am trying to get is something like :

The Results should be able to yield

GroupID GroupStatus GroupStatusText MemberID MemberStatus MemberStatusText
--- ---- ----- --- ---- ------
4 2 Resident 9 2 Resident
4 2 Resident 10 1 Referral
4 2 Resident 11 1 Referral
4 2 Resident 12 3 Non Resident

i am trying to do this with a single SQL query, i am sure it is possible, but cant finger out how to structure it. this is what i was thinking, but i am sure you can see its not going to work.

Lines 4 and 7 are the hangup i believe.
Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?

1 SELECT
2 PrimaryUserTable.GroupID,
3 PrimaryUserTable.GroupStatus,
4 xRefTable.MemberStatusText as GroupStatusText

5 SecondaryDataTable.MemberID,
6 SecondaryDataTable.MemberStatus,
7 xRefTable.MemberStatusText as MemberStatusText

8 LEFT JOIN SecondaryDataTable on (PrimaryUserTable.GroupID = SecondaryDataTable.GroupID)
9 LEFT JOIN xRefTable on (PrimaryUserTable.GroupStatus = xRefTable.MemberStatus)
10 LEFT JOIN xRefTable on (SecondaryDataTable.MemberStatus = xRefTable.MemberStatus)

11 WHERE GroupID = 4
12 ORDER BY GroupID, MemberIDSince both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?using table aliasesSELECT PrimaryUserTable.GroupID
, PrimaryUserTable.GroupStatus
, GroupXref.MemberStatusText as GroupStatusText
, SecondaryDataTable.MemberID
, SecondaryDataTable.MemberStatus
, MemberXref.MemberStatusText as MemberStatusText
FROM PrimaryUserTable
INNER
JOIN SecondaryDataTable
on SecondaryDataTable.GroupID = PrimaryUserTable.GroupID
INNER
JOIN xRefTable as GroupXref
on GroupXref.MemberStatus = PrimaryUserTable.GroupStatus
INNER
JOIN xRefTable as MemberXref
on MemberXref.MemberStatus = SecondaryDataTable.MemberStatus
WHERE PrimaryUserTable.GroupID = 4
ORDER
BY PrimaryUserTable.GroupID
, SecondaryDataTable.MemberID:)|||EXCELLENT !

exactly what i needed. the table alisases were just skipping from my mind that night.

THANK YOUsql

Duplicate Values Problem

I trying to insert values from a temporary table into a permanent table. Th
e
problem is the temporary table has duplicate UpdateTime values (issues with
the database used to populate the temporary table) and the UpdateTime is a
primary key in the permanent table.
Is there a way I can remove, or exclude, the duplicate values in the
temporary table before inserting the values into the permanent table?
RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
value with the larger RUN_SER_NO is the one to keep.
UpdateTime GRADE RUN_SER_NO
11-Aug-04 04:00 AA 634
11-Aug-04 05:00 AA 634
11-Aug-04 06:00 AA 634
11-Aug-04 07:00 VVV 636
11-Aug-04 07:00 VVV 635
11-Aug-04 08:00 VVV 635
11-Aug-04 08:00 VVV 636
11-Aug-04 09:00 VVV 636
11-Aug-04 09:00 VVV 635
11-Aug-04 10:00 VVV 635
11-Aug-04 10:00 VVV 636
11-Aug-04 11:00 VVV 636
11-Aug-04 12:00 VVV 636
11-Aug-04 13:00 VVV 636
11-Aug-04 14:00 VVV 636
11-Aug-04 15:00 VVV 636
Thanks in advance,
Raulselect UpdateTime from table_name group by UpdateTime having count(*) > 1
will give you the duplicates.You can remove them by using this query before
inserting into the permanent table.
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>|||How about:
SELECT update_time, grade, run_ser_no
FROM tbl t1
WHERE t1.run_ser_no = ( SELECT MAX( t2.run_ser_no )
FROM tbl t2
WHERE t2.UpdateTime = t1.UpdateTime
AND t1.grade = t2.grade ) ;
Anith|||On Tue, 8 Feb 2005 14:11:04 -0800, Raul wrote:

>I trying to insert values from a temporary table into a permanent table. T
he
>problem is the temporary table has duplicate UpdateTime values (issues with
>the database used to populate the temporary table) and the UpdateTime is a
>primary key in the permanent table.
>Is there a way I can remove, or exclude, the duplicate values in the
>temporary table before inserting the values into the permanent table?
Hi Raul,
SELECT UpdateTime, Grade, Run_ser_no
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.UpdateTime = a.UpdateTime
AND b.Run_ser_no > a.Run_ser_no)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all who replied!
These suggestions are really helpful.
Thanks again,
Raul
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>

Duplicate Values on Top Level of Dimension

Hey there community,

I have a problem, and i am lost as to how i can fix it.. I would be over joyed if someone can give me a hand in identiffy it.. I will try to explain below::

I have a cube for sales, that displays sales by a Market heiracy of Market->Division->Family->Item. When i drill down, sales numbers look fine..

I added in a budget file, which only specifys budgets down the the Division Level.. I have added the Budget Figure as a Measure Group. In the dimension usage tab, i specify the Division as the Granuality for the Budget Measure Group on the Market Dimension..

	Measure Groups
Dimensions	Standard Measure	Budget Measures	Freight Amount
Date	Date	Period	Date
Sales Person	Sales Person		Sales Person
Order Type	Order Type
Customer	Customer	Country	Customer ID
Market	Item	Division

When i build and deploy my cube, and i drag in the Market Heiracy, i add the sales fugures and the budget amounts. The top level, should split down on the Budget Figure, but i get the same value repeated all the way through like below:

Market	Sales Amount	Budget Amount
aaaaa	527434.08	3819283
bbbbb	1605726.50999999	3819283
cccccc	640.06	3819283
ddddd	1488549.15	3819283
eeeee	9107.59	3819283
Grand Total	3631457.39	3819283

when i Drill Down on the Market to the Division Level, the splits are correct, but the totals are all the same all the way down.. The should be the total of that market...

Market	Division	Sales Amount	Budget Amount
aaaaa	GP	24100.27	13244
	PT	503333.81	635552
	Total	527434.08	3819283
bbbbb	GP	333209.5	335234
	Other	167680.46	127426
	PT	1104836.55000001	1122647
	Total	1605726.50999999	3819283
cccccc	PT	640.06	0
	Total	640.06	3819283
ddddd	GP	790620.499999999	794611
	Other	11206.16	14831
	PT	686722.49	769930
	Total	1488549.15	3819283
eeeee	Other	9107.59	5808
	Total	9107.59	3819283
Grand Total		3631457.39	3819283

I would like the totals to be the total budget for each market.. I am stuck, i have tried some different things, but this is as far as can get..

If i choose the Market to be the Granuality for the Budget Measure Group, then i get the reverse of this.. The market splits are correct, but when i drill down to the Division Level, the values repeat..

Please can someone give me some ideas of what to try!

I can provide more data if need be.

Many thanks in advance!

Thanks Scott Lancaster

I reckon you don't have relationship defined between Market and Division. If you will make Market a member property of Division - everything should become fine (and keep Division as granularity for Budget measure group).|||

Thanks Mosha..

Could you please explain how i do this? Im pretty new to SSAS and have taught myself most of the way

Again.. very very much appriciated..

Thanks

|||In the dimension editor, drag Market attribute and drop it into Division attribute. This will create the needed relationship. If still in doubt, read documentation on the subject of "related attributes" or "member properties".|||

Thanks Mosha!!!!

your are a champion.. I have done this and it has all worked perfectly...

Again.. thanks mate..

Scotty

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

duplicate values

hey guys,

ive run into an issue and im not quite sure how to fix it.. let me explain

i have a cube, by market and i wish to show sales and freight costs.

The freight Fact table has no relationship to the market dimension as freigth is charged on customer order level, not item level.

I have data showing like the following:

Year

2006

Market

Sales

Freight Amount

Auto OEM

$7,115,315.22

$56,620.73

Auto Repl

$18,517,004.07

$56,620.73

Intercompany

$4,772,910.37

$56,620.73

Ind OEM

$12,039.74

$56,620.73

Ind Repl

$18,679,313.71

$56,620.73

Other

$84,263.19

$56,620.73

Grand Total

$49,180,846.30

$56,620.73

I would only like the freigth amount to show at the Grand Total Level. Is this possible? or, if not, is there a way you can combine two dimensions into 1? ie, combine a Freight Account dimemsion and the Market Dimension so that i can see Markets, then the different Freight Accounts by rows?

Thanks in advance,

Scotty

If you look in the cube structure tab in BIDS, you will see that each measure group has a property called IgnoreUnrelatedDimensions, this defaults to true which means that when you slice a measure by an unrelated measure you see the total amount (which is the behaviour you are seeing). Changing this property to false will mean that the measures will only display when they are either sliced by a related dimension or at the highest level.

It's hard to be sure, but from what you have said I don't think combining the two dimension would work.

Duplicate Values

I have a table which is a license holder table (i.e., plumbers, electricians etc...) There are some people who appear in the table more than once as they have more than 1 type of license. I am tasked with querying out 200 of these people a week for mailing a recruitment letter which I am doing using the following select statement:

SELECT TOP 200 Technicians.Name, Technicians.Address, Technicians.City, Technicians.State, Technicians.ZipCode, Technicians.LicenseType
FROM Technicians

My problem is that this doesn't deal with the duplicates and distinct won't work because I need to pass the license type and that's the one field that's always distinct while the name and adress fields duplicate.You'll need to determine which LicenseType to return in cases where there is more than one. This example returns the lowest LicenseType alphabetically:

SELECT TOP 200
Technicians.Name,
Technicians.Address,
Technicians.City,
Technicians.State,
Technicians.ZipCode,
min(Technicians.LicenseType) LicenseType
FROM Technicians
group by Technicians.Name,
Technicians.Address,
Technicians.City,
Technicians.State,
Technicians.ZipCode|||Why don't you break the structure into PersonMaster, LicenseMaster, and PersonLicense? Then you can do an insert ... select distinct into those tables accordingly. The rest is common sense.|||Yes, if you can solve your problem by improving your database schema that is always preferable.|||Assume name is enough (You may have to do the whole row).

Notice the duplicity of Data...You really have 2 tables. That would make the SQL even easier...

There's no substitute for a good design..

USE Northwind
GO

CREATE TABLE myTable99(
[Name] varchar(50)
, Address varchar(255)
, City varchar(50)
, State char(2)
, ZipCode varchar(10)
, LicenseType varchar(10)
)
GO

INSERT INTO myTable99(
[Name]
, Address
, City
, State
, ZipCode
, LicenseType)
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC123' UNION ALL
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC456' UNION ALL
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC789' UNION ALL
SELECT 'Blinddude','123 Main St','OhiYo','OH','00000','XXX123' UNION ALL
SELECT 'Blinddude','123 Main St','OhiYo','OH','00000','XXX456' UNION ALL
SELECT 'rdjabarov','123 Main St','San Antonio','TX','00000','EFG123'
GO

SELECT TOP 200
[Name]
, Address
, City
, State
, ZipCode
, LicenseType
FROM myTable99 o
WHERE EXISTS (SELECT
[Name]
, Address
, City
, State
, ZipCode
FROM myTable99 i
WHERE i.[Name] = o.[Name]
GROUP BY
[Name]
, Address
, City
, State
, ZipCode
HAVING COUNT(*) > 1)
GO

DROP TABLE myTable99
GO|||Brett, i am disappointed i'm not in there as a plumber or something

by the way, your query only pulls out people who are multi-licensed

i guess that is one way to interpret "these people" in the original question

i personally would not have interpreted it as 200 of people with more than one license, but rather, 200 people overall, but no individual more than once

once again, good specs are shown to be crucial before we go merrily traipsing down the WHERE EXISTS path...

;) ;) ;)

in any case, would your WHERE clause not work better like this, assuming you were actually interested in pick only multi-license people...
WHERE 1 < ( SELECT count(*)
FROM myTable99 i
WHERE i.[Name] = o.[Name] )it's a correlated subquery after all, so it shouldn't need grouping

p.s. where's that thread where we were talking about the DBA getting the shaft for poor design? i have a link i want to add to it|||Rudy,

There are some people who appear in the table more than once as they have more than 1 type of license.

I just felt that THESE meant THOSE:D

And yes I was debating you're syntax...but I figured dup rows less the license meant the same guy...

Either way...it's a poor design, which I'm sure they're stuck with...

Maybe an updateable view would be a good thing here..|||Originally posted by Brett Kaiser
Maybe an updateable view would be a good thing here.. Nahhh, an updatable view would be a work-around. Fixing the underlying problems in the schema would be the good thing in this case!

-PatP|||Unfortunately, the database is provided directly by the State Board of Licensing and constantly updated so fixing the schema is not really an option. Great suggesions by the way. Because I didn't have a lot of time when I first posted I created a stored procedure that found duplicate license holders and marked all but instance as having already been mailed so therefore my original query which looks for licensees that have not already been mailed works without locating those duplicates. As the State updates the database I'll import only the new rows into the database (with the added [Mailed] bit field) and rerun the stored proc to mark duplicates as having already been mailed leaving me with a distinct record set. Thanks again for all your help!sql

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.
SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
--
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:
> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate UID in PK column

Please excuse my noviceness, but how is it possible that I
can have duplicate records that are exactly the same in
every way when there is a PK field that is meant to be
unique?
Because this has happened, I've had to remove and re-index.
Can anyone please help this novice?That shouldn't happen, and I've never heard that it really happened. But it
is difficult to do anything or troubleshoot if you don't have a repro or
don't have the data anymore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Benjamin" <ben.jones@.trendwest.com.au> wrote in message
news:2c7301c3a8ad$61c25480$3101280a@.phx.gbl...
> Please excuse my noviceness, but how is it possible that I
> can have duplicate records that are exactly the same in
> every way when there is a PK field that is meant to be
> unique?
> Because this has happened, I've had to remove and re-index.
> Can anyone please help this novice?

duplicate the SQL login

I have some sql login but I have forgotten the password. I can't modify the password anyway. Is there anyway to copy the login to a new login so that I can use the new login to access the database which it is granted to?

i am not sure i got the problem correctly ........ but anyhow you can not have duplicate login... but what u can do is you can create script of the existing login and then change only the name and password ... keep all other setting... this is as good as cloning of this login...

BTW ... why you can not change the password

Madhu

Duplicate Text data

Anyone have a method for identifiying dupes in a text field?
coromokes wrote:
> Anyone have a method for identifiying dupes in a text field?
By dupes, you mean the existence of the same text value in more than one
row? If so, you can group on the text column and use a having clause to
test for dupes.
You'll need to convert to a varchar or nvarchar data type first, which
means you'll only get access to teh first 4,000 or 8,0000 characters for
the test. But maybe that's good enough.
Select CAST(TextDataCol as VARCHAR(8000))
From TableName
Group By CAST(TextDataCol as VARCHAR(8000))
Having COUNT(*) > 1
David Gugick
Imceda Software
www.imceda.com
sql

Duplicate tables in subscriber database

we are replicating a table in sql 2000, all seems to be going well, but there
seem to be twice as many tables in the subscriber database than on the
publisher. For Example: there is talble f0092 that has the owner as dbo &
sys7334. This is the case for each single table, thus my database on the
subscriber is almost twice as large. Can anyone tell me why this happening
and what i need to do to correct this isssue
1. What is the sys7334 user? Does it own the replication?
2. Have you tried 1) drop subscription; 2) delete the tables in the
subscription database; 3) re-add subscription ? Or try creating a new
database and subscribe to your publication from it; does the same thing
happen? Those duplicate tables could be left over from a previous attempt
at replication.
Mike
"Mark Alejandro" <MarkAlejandro@.discussions.microsoft.com> wrote in message
news:36FCE4B3-FDB4-4600-885C-8A4D9F66C9B3@.microsoft.com...
> we are replicating a table in sql 2000, all seems to be going well, but
there
> seem to be twice as many tables in the subscriber database than on the
> publisher. For Example: there is talble f0092 that has the owner as dbo &
> sys7334. This is the case for each single table, thus my database on the
> subscriber is almost twice as large. Can anyone tell me why this happening
> and what i need to do to correct this isssue

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my db2 (same structure).
Is there a tool to make this easy?
The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>
|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>
|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
Thanks
GYK

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my DB2 (same structure).
Is there a tool to make this easy?The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my DB2 (same structure).
> Is there a tool to make this easy?
>|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my DB2 (same structure).
> Is there a tool to make this easy?
>|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
Thanks
GYK

Duplicate tables

Hi
Is there a way for duplicating tables in two databases?
I have a db1 with 3 tables, and I would like to create 3 similar tables in
my db2 (same structure).
Is there a tool to make this easy?The easiest way, IMO, is just to script the tables using Query Analyzer and
create them in the new database. Right-click on the table in the Object
Browser, select Script Object to New Window As, and click Create. Now just
change databases and apply the script.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>|||If you wish to include indexes, constraints, triggers, etc, use SQL
Enterprise Manager, Right Click your database ->All tasks_>Generate SQL
Script
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:%23pD0Ogc8EHA.3476@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a way for duplicating tables in two databases?
> I have a db1 with 3 tables, and I would like to create 3 similar tables in
> my db2 (same structure).
> Is there a tool to make this easy?
>|||Hi Gonzalo,
Simplest way is using SELECT INTO (check BOL). This will create table
(without index, constraints, foreign keys...) with the data.
Other methods are:
(a) Using BCP tool
(b) Script the table, apply it and then use INSERT INTO
--
Thanks
GYK

Duplicate table structures to a new database

Hi all expert,

Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes

Many thanks for the help.

regards,

Use Enterprise Manager's Generate Script Feature, and execute the Generated SQL on your target server.

|||

you can also check Database Publishing Wizard.

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Madhu

|||

I want to done this using T-SQL. So what is the script it can be?

|||

There isn't a built-in t-sql command to script However, you can use sp_oa* and smo/dmo. You can check google for archive script.

As to just creating a new table based on the old one, you can just do this:

select top 0 *

into new_table

from old_table

Note that this method does not create constraints/dependencies on the new table.

|||

You can run a trace with profiler to see what Management Studio does to create the script, is a bit more than i expected, but if you really want to you should be able to replicate that on your own.

|||Manivannan gave you the answer. Use the Generate Script feature, it will make the script for you, and then you can re-use that script.

|||

Yes I know there is a Generate Script functions. But I want to schedule it and run on every month, therefore a pre-generated script must be prepared so that it can auto run on every month.

So, can this be done? Thanks a lot for the help.

regards

sql

Duplicate table

Hi,
In my program i need to duplicate a table in a current data base.
I'm thinkin' of reading the data base columns and then rows and so i
create another table
Is there any other easy and fast method with SQL Server 2005, because
my idea is so slow
I'm using VB 2005 Express with SQL Server 2005 Express
Thanks
To duplicate the table columns and data, just:
SELECT * INTO Newtable FROM Oldtable
However this will not copy indexes, access privileges, etc.
To get everything you must script the table to create it, and then add the
data, perhaps with
INSERT INTO NewTable SELECT * FROM Oldtable
Of course, using column names instead of * would be a better practice.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Omar Abid" <omar.abid2006@.gmail.com> wrote in message
news:1181578789.815330.212000@.q69g2000hsb.googlegr oups.com...
> Hi,
> In my program i need to duplicate a table in a current data base.
> I'm thinkin' of reading the data base columns and then rows and so i
> create another table
> Is there any other easy and fast method with SQL Server 2005, because
> my idea is so slow
> I'm using VB 2005 Express with SQL Server 2005 Express
> Thanks
>
|||> INSERT INTO NewTable SELECT * FROM Oldtable
> Of course, using column names instead of * would be a better practice.
And might be necessary, e.g. for IDENTITY/ROWVERSION...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Duplicate table

Hi,
In my program i need to duplicate a table in a current data base.
I'm thinkin' of reading the data base columns and then rows and so i
create another table
Is there any other easy and fast method with SQL Server 2005, because
my idea is so slow
I'm using VB 2005 Express with SQL Server 2005 Express
ThanksHi
On Jun 11, 5:19 pm, Omar Abid <omar.abid2...@.gmail.com> wrote:
> Hi,
> In my program i need to duplicate a table in a current data base.
> I'm thinkin' of reading the data base columns and then rows and so i
> create another table
> Is there any other easy and fast method with SQL Server 2005, because
> my idea is so slow
> I'm using VB 2005 Express with SQL Server 2005 Express
> Thanks
If you just want to create copy the data then you could use
SELECT *
INTO #temptable
FROM Mytable
Which will create a new table with the contents of the old table, but
the new table will not have the constraints such as primary or foreign
keys and indexes that the original table had. If you dont want the
table but just the columns use a where clause that will never be
satisfied such as
WHERE 1 = 0
Alternatively you can use SMO see Allen Whites posts in the thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=876099&SiteID=1
John

duplicate table

Hi,

I have a table with data in it, (tblT1)

A. How do I create a table tblT2 with the same columns in tblT1, but without data in (blank tblT2 table with columns).

B. And also how do I copy the enitre table's data (tblT1) into tblT2.

Thank you,

Give a look in books online to SELECT ... INTO.

Here are some previous posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=126194&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307375&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=691090&SiteID=1

|||

Got it . I will check it out.

Thank you.