Removing IDENTITY from existing column

I have been testing some code that has been inserting large amounts of data
into some tables. All of the tables have a Primary Key that is also an
Identity. After a bad run, I go through and DELETE all of the entries in
each table (I would TRUNCATE, but they all have at least three other tables
referencing them and you can't TRUNCATE a linked table). Only problem is
that the IDs are getting extremely large and make it harder for me to
determine if the code is working correctly. In the TSQL that I use to DELET
E
the contents of the tables, I would like to set the IDENTITY to NO, then bac
k
to YES so that the IDs increment from 1 again instead of some very large
number. The only way that I can see is to drop the column and then add it
again. I am not sure how well this would work since I have lots of foreign
keys referencing these PKs.
Thanks in advance.
--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsDrop the IDENTITY and not the column, then put the IDENTITY back on the
column -- one way.
Owen
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||See "DBCC CHECKIDENT" in BOL.
AMB
"Chris Lieb" wrote:

> I have been testing some code that has been inserting large amounts of dat
a
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other table
s
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to DEL
ETE
> the contents of the tables, I would like to set the IDENTITY to NO, then b
ack
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of foreig
n
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Try: DBCC CHECKIDENT (tableName)
Look it ub in BOL.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Use
DBCC CHECKIDENT ('table_name',RESEED,0)
to reset the IDENTITY value.
David Portas
SQL Server MVP
--|||I tried to add IDENTITY to an existing column, and it didn't work. I was
told on this forum that it can't be done.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
> Drop the IDENTITY and not the column, then put the IDENTITY back on the
> column -- one way.
> Owen
> "Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
> news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>|||Strange. I do that all the time (using Enterprise Manager)...
Owen
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
>I tried to add IDENTITY to an existing column, and it didn't work. I was
>told on this forum that it can't be done.
>
> "Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
> news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
>|||Strange that you use Enterprise Manager to make changes to table
structures :-)
Paul is in fact correct that you cannot do this with a single statement
in TSQL. One of the (many) problems with using Enerprise Manager is
that to achieve this it will drop your table, constraints and indexes,
recreate and then copy all your data over. Definitely not recommended
on a system that's in use!
David Portas
SQL Server MVP
--|||That's because behind the scenes EM will jump through the hoops
of creating a temp table copy of the original table, creating a
new table with identity column assigned, inserting data from the
the temp table, and then dropping the temp table. There's no
easy way of doing it through Query Analyzer other than
what I've outlined.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
an
then
large
add
>|||EM does a lot of work behind the scenes.
A SIMPLE example on a table with no foreign key references where Identity is
removed:
It creates a new table, copies the data, drops the old table and renames the
new one.
If there where foreign key references, EM has to handle this too.
In EM, you have an icon in the table design view to save the script when you
make a change and before committing it.
Try it (on a test table of course).
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...

Removing Duplicates.

Here is the problem im trying to show Date and time for my selection, when i use the following code it shows the Date and time but leaves dups

SELECT DISTINCT(Item), OutDateTime_dt FROM Database Where AFTID_int = 2149 AND CID_int = 0 AND MID_int = 0 AND Item <> '' AND OutDateTime_dt >= '09/17/2003' AND OutDateTime_dt < '9/24/2003' Order by OutDateTime_dt Desc

However, if i remove the "OutDateTime_dt" from the first line,
leaving this

SELECT DISTINCT(Item) FROM Database

It removes the duplicates and works fine but doesnt give me the time as i need as well or date. Please help.If you only want one row per Item, and each Item may have many OutDateTime values, then you would have to choose which OutDateTime to display, e.g. the highest:

SELECT Item, MAX(OutDateTime_dt) FROM Database Where AFTID_int = 2149 AND CID_int = 0 AND MID_int = 0 AND Item <> '' AND OutDateTime_dt >= '09/17/2003' AND OutDateTime_dt < '9/24/2003' GROUP BY Item;

removing duplicate rows

I am not able to figure out a script that removes duplicates if for the same
SSNs, the startdate and a code is the same.
sample data
SSN startdate code
123456789 11-12-1980 2540
456789123 14-5-1965 1236
123456789 02-09-1980 7456
789456132 06-07-1950 2589
123456789 11-12-1980 2540
What I want deleted is here is :
123456789 11-12-1980 2540 (just one of the two records)
Any help would be great
thanksAs long as you don=B4t have any identifier which can differ between the
rows there are onl workarounds for that:
1=2E Include a identifier to differ (like an identity column), then use
the follwing code:
DELETE YourTable FROM YourTable T
INNER JOIN
(
SELECT identifiercolumn from YourTable
GROUP BY SSN,startdate,code
HAVING COUNT(*) > 1
) SuQUery
ON SubQuery.identifiercolumn =3D T.identifiercolumn
2=2E (Preferable one) Copy one of the duples to a temp table, delete all
duplicates and reinsert the data from the temp table.
3=2E Avoid duplicates !
HTH, Jens Suessmeyer.|||Jens
There is a sequence number in the table that is unique. But It will not
matter which one we delete I would prefer the number that has a greater valu
e
(as in the seqnumber). Also I am pretty new to programming in SQL so if
possible can u actually show me how to crerate an identifier Copy one of the
duples to a temp table, delete all duplicates and reinsert the data from the
temp table.And avoid duplicates !
Thanks for your help
Amit
"Jens" wrote:

> As long as you don′t have any identifier which can differ between the
> rows there are onl workarounds for that:
> 1. Include a identifier to differ (like an identity column), then use
> the follwing code:
> DELETE YourTable FROM YourTable T
> INNER JOIN
> (
> SELECT identifiercolumn from YourTable
> GROUP BY SSN,startdate,code
> HAVING COUNT(*) > 1
> ) SuQUery
> ON SubQuery.identifiercolumn = T.identifiercolumn
> 2. (Preferable one) Copy one of the duples to a temp table, delete all
> duplicates and reinsert the data from the temp table.
> 3. Avoid duplicates !
> HTH, Jens Suessmeyer.
>|||Hi There,
Having a table with all column values identical for two or more rows
means that there are no constraints . Are you trying to manage history
and usable data in the same table?
I Think this query might help you.
Delete from YourTableName Where RowID NOT IN (Select MIN(ROWID) From
YourTableName Group By FieldList,......)
The group by will have all the fields except ROWID (or Unique
identifier)
I still say Aviod duplicates and have constraints on the table . Manage
history and usable data in different table (if this is the case).
With Warm regards
Jatinder SIngh

Removing and adding articles by code

Hello there
On my live replication, I need, sometimes, to change schema of replicated
tables.
Because, as long as the table is replicated, i can't change the schema, I
simple do:
1. Remove the article from the parent table
2. change the schema of the current table
3. add it again to the schema
I would like to do this with code.
Is there a way to do this?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
please take a look at this article:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)