Remove identity property of a primary key

Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:

> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>

remove IDENTITY in sql server

suppose my table is
create table TEST (
id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)
I want to remove IDENTITY(1,1) from id without dropping tableHi ankur
I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.|||I am toatally new to sql server

so

initailly our project manager has ordered me to put auto incrment

but

now

PM has said to remove auto increment

I am in bit trouble dont know to remove auto increment

so

I asked help from you

help me please|||Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.|||beh.

just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!|||why don't you want to drop the table?

create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one

something about this whole scenario does not smell right

the project manager is actually you, isn't she|||Think bigger picture

If you still need to retain the id, what is the PM's plan to manage new id's?

And do you have a natural key?|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data

No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...

As a matter of fact, do you know how save a script in Enterprise Mangler?

go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself

Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old

Again, how many rows are we talking about

Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right...and then do

SET IDENTITY_INSERT ON

But that's a hack|||And do you have a natural key?as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM|||I want to remove IDENTITY(1,1) from id without dropping tableDropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.

The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.

There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.

-PatP|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html|||Ahh I see!
Thanks Poots :)

I guess as long as you're aware of the potential problems you can use your best judgement per case.|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO|||see attachment set IDENTITY to NOJust curioius, but do you have any clue what setting IDENTITY to "NO" on this screen will actually do? It will produce exactly the result you have requested, using exactly the method that you wanted to avoid.

As long as you are happy with what you've found, that's fine. It just isn't what you requested! ;)

-PatP|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO

To quote a Guiness Stout Commercial...

"Brilliant!"|||go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

That sounds oddly familiar

Remove Identity

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi.
You can use something as this:
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL
Hermilson T.
****************************************
**********************
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> ALTER
for
> rights.
>

Remove Identity

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi
You can use something as this
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL
[f_city_desc] [varchar] (50) DEFAULT (''
) ON [PRIMARY
G
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NUL
Hermilson T
*************************************************************
I have created an identity column in a table.Now i want to remove th
identity property from the column by using T-SQL script
Can anyone tell How
Thanking you
Toee|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > You can use something as this:
> >
> > Create tha table with Identity...
> >
> > CREATE TABLE [City]
> > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [f_city_desc] [varchar] (50) DEFAULT ('')
> > ) ON [PRIMARY]
> > GO
> >
> > And to Modify the column, erasing the property of the Identity.
> >
> > Alter TABLE City
> > Alter column f_city_id [int] NOT NULL
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > > You can use something as this:
> > >
> > > Create tha table with Identity...
> > >
> > > CREATE TABLE [City]
> > > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > > [f_city_desc] [varchar] (50) DEFAULT ('')
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > And to Modify the column, erasing the property of the Identity.
> > >
> > > Alter TABLE City
> > > Alter column f_city_id [int] NOT NULL
> >
> > You can't clear the IDENTITY property from the table column using the
> ALTER
> > TABLE statement. You have to re-create the database and move the data,
for
> > example:
> >
> > BEGIN TRANSACTION
> > CREATE TABLE dbo.TemporaryTable
> > (
> > id int NOT NULL
> > ) ON [PRIMARY]
> > GO
> > IF EXISTS(SELECT * FROM dbo.YourTable)
> > EXEC('INSERT INTO dbo.TemporaryTable(id)
> > SELECT id FROM dbo.YourTable TABLOCKX')
> > GO
> > DROP TABLE dbo.YourTable
> > GO
> > EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> > GO
> > COMMIT
> >
> > sincerely,
> > --
> > Sebastian K. Zaklada
> > Skilled Software
> > http://www.skilledsoftware.com
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
>

Remove Grand Total in Pivot Table issue using asp.net

Hi,

I am trying to remove the grad total amount from the pivot table list with asp.net,programing. The data I called from cube, I have rows,columns and data axis values, when I place my measure in to data axis it autometically created sub total and grand totals, I want remove those. I could not found any property or method to remove that.

Please help me on this.

Hi Please help, I still not got any answer.

Remove files from databases

Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:

1- Drop the table - OK

2- Drop the filegroup - OK

3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.

When I query a catalog view with this query:

Select*FromSys.Database_Files

I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.

This is the code I use:

Use [TESTING ]

Go

-- Add a FileGroup to the Database

AlterDatabase TESTING AddFileGroup FG01

Go

-- Add a file to a FileGroup

AlterDatabase TESTING AddFile(

NAME= TESTING_DATA01,

FILENAME='D:\Sql Server\Data\Testing_Data01.ndf',

SIZE= 1 MB,

MAXSIZE= 10 MB,

FILEGROWTH= 1 MB

)ToFileGroup FG01

Go

-- Create the table using a specific FileGroup

CreateTable TABLE1 (

Id IntNotNull,

FirstName VarChar(30)NotNull,

LastName VarChar(30)NotNull,

BirthDate SmallDateTime

)

On FG01

So far everything works ok, but in the next code there is an error:

-- Delete the table

Drop Table TABLE1

Go

-- Remove a FileGroup from the Database

AlterDatabase TESTING Remove FileGroup FG01

Go

-- Try to remove the file

AlterDatabase TESTING Remove File TESTING_DATA01

When I try to remove the file there is an error:

"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"

You simply need to change the order of your Remove statements to remove the file BEFORE you remove the filegroup.|||Thanks for the answer, but how can I delete the entries in the Sys.Database_Files that already exists ?

Remove Duplicate value from One Cloumn Table

I am working SQL Server 2005 and One Table Which contain only one column without primary key
Now I want to remove all duplicate value from that table with only single query
Here are a couple options:
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
|||

Thank

Here happens like that

1) Select Distinct row from the original table and store it into tempory table

2) delete all rows from original table

3) copy the all rows from tempory table (where distinct rows inserted)

But I want to do this thing using single Delete Query
Can we do this by using only single delete query

sql

Remove duplicate rows from table

I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove...
my tables is like that

Names
----
Rakesh
Rakesh
Rakesh Kumar Sharma
Rakesh Kumar Sharma
Baburaj
Raghu
Raghu

and Output of query should be like that
Names
----
Rakesh
Rakesh Kumar Sharma
Baburaj
Raghu

Thanks in advanceSELECT * FROM table1
UNION
SELECT * FROM table1
or
SELECT distinct name FROM table1

...;)

Plz give the whole structure of your table,then only we can help you.Plz read the sticky above in this forum.|||Hi,

You can find three methods with samples for removing dublicated records from tables at article http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

One method is using "SET ROWCOUNT", an other method uses the "TOP". And the last way of solving this problem is getting use of a temporary "IDENTITY" column.

I hope you find it useful

Eralper
http://www.kodyaz.com|||As always, make a back-up first!
select distinct *
into #temptable
from yourtable

delete from yourtable

insert into yourtable
select *
from #temptable

drop table #temptable

Remove duplicate rows from a table

Hi guys

I have been using SQL server 2005. I have got a huge table with about 1 million rows.

Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that?

Is there a query in SQL to remove duplicate rows?

thanks

Mita

Mita:

I would start by modifying the table so that it becomes impossible to have duplicate rows. That is, you want to make it so that no two rows will ever be exactly identical. By doing this you will always have a method of selecting records according to the property that makes them unique. The easiest ways to make table rows unique are to use either (1) an identity column or (2) a unique identifier column. Look in books online for a discussion. Once you have a method of keying your records you will be able to delete records according to this record key.

Dave

|||

Hi,

refer below threads discussing same topic

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3632

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18529

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14484

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13882

Hemantgiri S. Goswami

remove duplicate rows

i have a table with NO constraints and duplicate rows(e.g one row is inserted four times) now I want to remove the rows in such a way that only one row from the duplicate rows should stay in the table. Is there any query to solve this problem.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Here is your solution:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q139444
"enoch jadhav" <enochjadhav@.myway.com> wrote in message
news:ejrKmIblDHA.424@.TK2MSFTNGP10.phx.gbl...
> i have a table with NO constraints and duplicate rows(e.g one row is
inserted four times) now I want to remove the rows in such a way that only
one row from the duplicate rows should stay in the table. Is there any query
to solve this problem.
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Remove Duplicate Rows

I've got the following table data:

116525.99
116520.14
129965.03
129960.12
129967.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!dale...@.gmail.com wrote:
> I've got the following table data:
> 116525.99
> 116520.14
> 129965.03
> 129960.12
> 129967.00
> And I need to write a query to return only rows 2 and 4, since the
> remaining rows have duplicate IDs. I've tried the Group By, but am
> having no luck.
> Thanks!

What do you mean by "rows 2 and 4"? Those numbers refer to positions in
the list of values you posted, but SQL Server knows nothing about that
because tables in SQL have no logical order at all. In other words you
haven't given enough information to answer your question.

If these are the only two columns you have then probably the best you
can do is:

SELECT col1, MIN(col2) AS col2
FROM your_table
GROUP BY col1 ;

or:

SELECT col1, MAX(col2) AS col2
FROM your_table
GROUP BY col1 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Why 2 and 4? Why not 1 & 3 or 1 & 5? What are you using as your
discriminator?

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<dale.zjc@.gmail.com> wrote in message
news:1144699376.547275.246250@.t31g2000cwb.googlegr oups.com...
I've got the following table data:

11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!|||Thanks for the quick response!

Here's my complete query:

SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE

FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID

Group By Job.JobID

But I'm getting the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

David Portas wrote:
> dale...@.gmail.com wrote:
> > I've got the following table data:
> > 116525.99
> > 116520.14
> > 129965.03
> > 129960.12
> > 129967.00
> > And I need to write a query to return only rows 2 and 4, since the
> > remaining rows have duplicate IDs. I've tried the Group By, but am
> > having no luck.
> > Thanks!
> What do you mean by "rows 2 and 4"? Those numbers refer to positions in
> the list of values you posted, but SQL Server knows nothing about that
> because tables in SQL have no logical order at all. In other words you
> haven't given enough information to answer your question.
> If these are the only two columns you have then probably the best you
> can do is:
> SELECT col1, MIN(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> or:
> SELECT col1, MAX(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --|||dale...@.gmail.com wrote:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID
> But I'm getting the following error:
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Object.Name' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Data.Time' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Job.KSName' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.

Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(dale.zjc@.gmail.com) writes:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID

This is possible correct version of your query, but most probably not.
It's just a piece of guesswork.

SELECT o.Name, j.JobID, mx.NewValue,
DATEADD(ss, d.[Time], '1 jan 1970') AS EventDate,
j.KSName, GETDATE() AS CURDATE
FROM (SELECT j.JobID, NewValue = MAX(d.[Value])
FROM DataHeader dh
JOIN Job j ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID) AS mx
JOIN Job j ON mx.JobID = j.JobID
JOIN DataHeader dh ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID
JOIN Object o ON o.ObjID = j.MachineObjID
GROUP BY j.JobID

For this type of questions it helps if you include descriptions of
your tables, including keys. Preferably in form of CREATE TABLE
statements. Sample data is also a good idea, even better if as
INSERT statements, as that makes it easy to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

REMOVE DUPLICATE ROWS

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

DiegoHi

There are several ways of doing this... You can select distinct rows into a
temporary table (See DISTINCT in books online), clear out your main table
and then re-populate it. If you have an differentiating column then you can
use that to delete rows that are not (say) the minimum value of that
column, or you could stop rows being put in the table in the first place by
having a unique index or primary key over the columns that should be
distinct, or using a not exists clause when inserting the data.

E.g.
..
SELECT DISTINCT *
INTO #SimpleExample
FROM Test

TRUNCATE TABLE TEST

INSERT INTO TEST ( c1, c2, c3 )
SELECT * FROM #SimpleExample

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc which would have
been useful. See http://www.aspfaq.com/etiquette.asp?id=5006

John

"Diego Rey" <diegobph@.yahoo.com> wrote in message
news:e09be785.0412042052.52c8c7e5@.posting.google.c om...
> Hi everyone.
> How can I get the unique row from a table which contains multiple rows
> that have exactly the same values.
> example:
> create table test (
> c1 as smallint,
> c2 as smallint,
> c3 as smallint )
> insert into test values (1,2,3)
> insert into test values (1,2,3)
> i want to remove whichever of the rows but I want to retain a single
> row.
> TIA
> Diego|||John Bell (jbellnewsposts@.hotmail.com) writes:
> As there is no AS in a CREATE TABLE statement you will have problems with
> this DDL, there is also no information regarding PKs etc

Obviously, if he has identical rows in his table, there is no primary key.

Which all good tables in relational database is supposed to have, and thus
this explains why this operation is not a trivial one to perform. You are
simply not supposed to wind up in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Obviously, if he has identical rows in his table, there is no primary
>> key...Which all good tables in relational database is supposed to have,

Is that a "relational heresy" or a "relational orthodoxy" or an "attempt to
appease the ideas of relational theory" ?( ..kidding :-) )

--
Anithsql

Remove duplicate records after importing via SSIS

Hello all,
I have some phone logs that I would like to import into table on a daily or
periodic basis. I would like to be able to elliminate any duplicate records
that it imports when it appends it to the table. Is there some T-SQL that I
can run that would help with this situation? I DO have one particular
field that has a unique ID for each record, so it *should* be pretty easy, if
I only knew what I was doing. ;-)
--
SketchySince you have a unique key and existing data, set up the insert using a not
exists clause
insert realtable
select ..
from holdingtable ht
where not exists (select * from realtable rt where rt.keyfield =ht.keyfield)
TheSQLGuru
President
Indicium Resources, Inc.
"sketchy" <sketchy@.discussions.microsoft.com> wrote in message
news:AD538EA5-A55B-45C1-9992-D0AAFD0AD0C6@.microsoft.com...
> Hello all,
> I have some phone logs that I would like to import into table on a daily
> or
> periodic basis. I would like to be able to elliminate any duplicate
> records
> that it imports when it appends it to the table. Is there some T-SQL that
> I
> can run that would help with this situation? I DO have one particular
> field that has a unique ID for each record, so it *should* be pretty easy,
> if
> I only knew what I was doing. ;-)
> --
> Sketchy|||On Thu, 23 Aug 2007 07:22:30 -0700, sketchy
<sketchy@.discussions.microsoft.com> wrote:
>Hello all,
>I have some phone logs that I would like to import into table on a daily or
>periodic basis. I would like to be able to elliminate any duplicate records
>that it imports when it appends it to the table. Is there some T-SQL that I
>can run that would help with this situation? I DO have one particular
>field that has a unique ID for each record, so it *should* be pretty easy, if
>I only knew what I was doing. ;-)
Create a staging table that matches the input data layout. Import the
new data into the staging table. Insert the data from the staging
table into the production table with a WHERE NOT EXISTS test to skip
the duplicates. Truncate the staging table before loading and
processing the next set of data.
The only tricky part left is if there are duplicates in the staging
table itself, as the NOT EXISTS test only prevents inserting them when
they are already there. If the entire row is identical that can be
handled with a DISTINCT. If the row has differences in some column
other than the unique ID you need to provide more information on which
one to choose (as well as raising questions about the entire process.)
Roy Harvey
Beacon Falls, CT|||sketchy,
It would be better if you can avoid inserting the duplicated rows, as
TheSQLGuru stated. You need to work with the group of columns that make the
row unique, let us suppose that they are (c1, c2, c3), then:
delete dbo.t1
where exists (
select *
from dbo.t1 as a
where a.c1 = dbo.t1.c1
and a.c2 = dbo.t1.c2
and a.c3 = dbo.t1.c3
and a.[id] < dbo.t1.[id]
)
-- or
-- 2005
;with cte
as
(
select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
as rn
from dbo.t1
)
delete cte
where rn > 1;
AMB
"sketchy" wrote:
> Hello all,
> I have some phone logs that I would like to import into table on a daily or
> periodic basis. I would like to be able to elliminate any duplicate records
> that it imports when it appends it to the table. Is there some T-SQL that I
> can run that would help with this situation? I DO have one particular
> field that has a unique ID for each record, so it *should* be pretty easy, if
> I only knew what I was doing. ;-)
> --
> Sketchy|||Wow guys. this is all great information. I never thought of having a
staging table. Let me soak this in a bit and if I have any more questions, I
know who to ask.
--
Sketchy
"Alejandro Mesa" wrote:
> sketchy,
> It would be better if you can avoid inserting the duplicated rows, as
> TheSQLGuru stated. You need to work with the group of columns that make the
> row unique, let us suppose that they are (c1, c2, c3), then:
> delete dbo.t1
> where exists (
> select *
> from dbo.t1 as a
> where a.c1 = dbo.t1.c1
> and a.c2 = dbo.t1.c2
> and a.c3 = dbo.t1.c3
> and a.[id] < dbo.t1.[id]
> )
> -- or
> -- 2005
> ;with cte
> as
> (
> select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
> as rn
> from dbo.t1
> )
> delete cte
> where rn > 1;
>
> AMB
> "sketchy" wrote:
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily or
> > periodic basis. I would like to be able to elliminate any duplicate records
> > that it imports when it appends it to the table. Is there some T-SQL that I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy, if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"Roy Harvey" wrote:
> On Thu, 23 Aug 2007 07:22:30 -0700, sketchy
> <sketchy@.discussions.microsoft.com> wrote:
> >Hello all,
> >
> >I have some phone logs that I would like to import into table on a daily or
> >periodic basis. I would like to be able to elliminate any duplicate records
> >that it imports when it appends it to the table. Is there some T-SQL that I
> >can run that would help with this situation? I DO have one particular
> >field that has a unique ID for each record, so it *should* be pretty easy, if
> >I only knew what I was doing. ;-)
> Create a staging table that matches the input data layout. Import the
> new data into the staging table. Insert the data from the staging
> table into the production table with a WHERE NOT EXISTS test to skip
> the duplicates. Truncate the staging table before loading and
> processing the next set of data.
> The only tricky part left is if there are duplicates in the staging
> table itself, as the NOT EXISTS test only prevents inserting them when
> they are already there. If the entire row is identical that can be
> handled with a DISTINCT. If the row has differences in some column
> other than the unique ID you need to provide more information on which
> one to choose (as well as raising questions about the entire process.)
> Roy Harvey
> Beacon Falls, CT
>|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"TheSQLGuru" wrote:
> Since you have a unique key and existing data, set up the insert using a not
> exists clause
> insert realtable
> select ..
> from holdingtable ht
> where not exists (select * from realtable rt where rt.keyfield => ht.keyfield)
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "sketchy" <sketchy@.discussions.microsoft.com> wrote in message
> news:AD538EA5-A55B-45C1-9992-D0AAFD0AD0C6@.microsoft.com...
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily
> > or
> > periodic basis. I would like to be able to elliminate any duplicate
> > records
> > that it imports when it appends it to the table. Is there some T-SQL that
> > I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy,
> > if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy
>
>|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"Alejandro Mesa" wrote:
> sketchy,
> It would be better if you can avoid inserting the duplicated rows, as
> TheSQLGuru stated. You need to work with the group of columns that make the
> row unique, let us suppose that they are (c1, c2, c3), then:
> delete dbo.t1
> where exists (
> select *
> from dbo.t1 as a
> where a.c1 = dbo.t1.c1
> and a.c2 = dbo.t1.c2
> and a.c3 = dbo.t1.c3
> and a.[id] < dbo.t1.[id]
> )
> -- or
> -- 2005
> ;with cte
> as
> (
> select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
> as rn
> from dbo.t1
> )
> delete cte
> where rn > 1;
>
> AMB
> "sketchy" wrote:
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily or
> > periodic basis. I would like to be able to elliminate any duplicate records
> > that it imports when it appends it to the table. Is there some T-SQL that I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy, if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy|||On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
<sketchy@.discussions.microsoft.com> wrote:
>Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
>1. I have my original table, which will be used for my reporting needs,
>called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
>"Field3" etc. but the main field that has the unique info in it is called
>"GlobalCallID"
>2. I have created a new table, which will be used for my staging of data,
>called 'phonelogstaging'. This database has the EXACT same field names and
>types.
>3. I've set up SSIS to import my log files into the staging table
>("phonelogstaging"). It wipes out any previous data in this staging table,
>so there is no possibility of duplicates in this table. Everything is
>working good there.
>Both tables have a field called GlobalCallID that has the unique number in
>it that I should be able to check against.
>So considering the above, how would my statement look?
Assuming that data already in the table does not need to be refreshed,
only new data added:
INSERT phones
SELECT <column list>
FROM phonelogstaging as A
WHERE NOT EXISTS
(SELECT * FROM phones as B
WHERE A.GlobalCallID = B.GlobalCallID)
If the incoming data itself has duplicates, add DISTINCT after the
word SELECT.
If you need to refresh the rest of the columns of matching rows from
the staging data, you would also run the following BEFORE the command
above.
UPDATE phones
SET col1 = A.col1,
col2 = A.col2
FROM phonelogstaging as A
WHERE phones.GlobalCallID = A.GlobalCallID
Roy Harvey
Beacon Falls, CT|||Hi Roy,
Thank you SO MUCH for your quick response.
1. Yes, only new data needs to be added. No data needs to be refreshed, so
that's good.
2. One little wrinkle in the plan is that much to my dismay, it does appear
that the "GlobalCallID" field isn't necessarily a unique number, but I do
have a field adjacent to it ("CallNumber") where no records would ever have
the same combination of the two. How would you ammend your last statement to
accomodate for this? (Ugh, I know... that probably doesn't make things
simpler)
--
Sketchy
"Roy Harvey" wrote:
> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
> <sketchy@.discussions.microsoft.com> wrote:
> >Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
> >
> >1. I have my original table, which will be used for my reporting needs,
> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
> >"Field3" etc. but the main field that has the unique info in it is called
> >"GlobalCallID"
> >
> >2. I have created a new table, which will be used for my staging of data,
> >called 'phonelogstaging'. This database has the EXACT same field names and
> >types.
> >
> >3. I've set up SSIS to import my log files into the staging table
> >("phonelogstaging"). It wipes out any previous data in this staging table,
> >so there is no possibility of duplicates in this table. Everything is
> >working good there.
> >
> >Both tables have a field called GlobalCallID that has the unique number in
> >it that I should be able to check against.
> >
> >So considering the above, how would my statement look?
> Assuming that data already in the table does not need to be refreshed,
> only new data added:
> INSERT phones
> SELECT <column list>
> FROM phonelogstaging as A
> WHERE NOT EXISTS
> (SELECT * FROM phones as B
> WHERE A.GlobalCallID = B.GlobalCallID)
> If the incoming data itself has duplicates, add DISTINCT after the
> word SELECT.
> If you need to refresh the rest of the columns of matching rows from
> the staging data, you would also run the following BEFORE the command
> above.
> UPDATE phones
> SET col1 = A.col1,
> col2 = A.col2
> FROM phonelogstaging as A
> WHERE phones.GlobalCallID = A.GlobalCallID
> Roy Harvey
> Beacon Falls, CT
>|||Just add the new field that DOES make a row unique to the not exists clause:
> INSERT phones
> SELECT <column list>
> FROM phonelogstaging as A
> WHERE NOT EXISTS
> (SELECT * FROM phones as B
> WHERE A.GlobalCallID = B.GlobalCallID
and a.CallNumber = b.CallNumber)
You can use as many fields as you need to ensure uniqueness.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sketchy" <sketchy@.discussions.microsoft.com> wrote in message
news:CCA5542F-5C15-413A-8B8F-D2200C1BEAD3@.microsoft.com...
> Hi Roy,
> Thank you SO MUCH for your quick response.
> 1. Yes, only new data needs to be added. No data needs to be refreshed,
> so
> that's good.
> 2. One little wrinkle in the plan is that much to my dismay, it does
> appear
> that the "GlobalCallID" field isn't necessarily a unique number, but I do
> have a field adjacent to it ("CallNumber") where no records would ever
> have
> the same combination of the two. How would you ammend your last statement
> to
> accomodate for this? (Ugh, I know... that probably doesn't make things
> simpler)
> --
> Sketchy
>
> "Roy Harvey" wrote:
>> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
>> <sketchy@.discussions.microsoft.com> wrote:
>> >Okay, so here is what I have done. (this is a SQL 2005 DB by the
>> >way...)
>> >
>> >1. I have my original table, which will be used for my reporting needs,
>> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
>> >"Field3" etc. but the main field that has the unique info in it is
>> >called
>> >"GlobalCallID"
>> >
>> >2. I have created a new table, which will be used for my staging of
>> >data,
>> >called 'phonelogstaging'. This database has the EXACT same field names
>> >and
>> >types.
>> >
>> >3. I've set up SSIS to import my log files into the staging table
>> >("phonelogstaging"). It wipes out any previous data in this staging
>> >table,
>> >so there is no possibility of duplicates in this table. Everything is
>> >working good there.
>> >
>> >Both tables have a field called GlobalCallID that has the unique number
>> >in
>> >it that I should be able to check against.
>> >
>> >So considering the above, how would my statement look?
>> Assuming that data already in the table does not need to be refreshed,
>> only new data added:
>> INSERT phones
>> SELECT <column list>
>> FROM phonelogstaging as A
>> WHERE NOT EXISTS
>> (SELECT * FROM phones as B
>> WHERE A.GlobalCallID = B.GlobalCallID)
>> If the incoming data itself has duplicates, add DISTINCT after the
>> word SELECT.
>> If you need to refresh the rest of the columns of matching rows from
>> the staging data, you would also run the following BEFORE the command
>> above.
>> UPDATE phones
>> SET col1 = A.col1,
>> col2 = A.col2
>> FROM phonelogstaging as A
>> WHERE phones.GlobalCallID = A.GlobalCallID
>> Roy Harvey
>> Beacon Falls, CT|||It works!!!! ...Thanks guys for all of your help in this. I hope for some
good computer karma to come your way.
One interesting little tidbit is that I was unable to get it to work by
specifying all of the fields in the Select statement. No matter what I did,
it always came back with the error of:
Insert Error: Column name or number of supplied values does not match table
definition.
It was odd because that table was the exact same as the other one. So I
just changed it to "*" and all worked well.
--
Sketchy
"TheSQLGuru" wrote:
> Just add the new field that DOES make a row unique to the not exists clause:
>
> > INSERT phones
> > SELECT <column list>
> > FROM phonelogstaging as A
> > WHERE NOT EXISTS
> > (SELECT * FROM phones as B
> > WHERE A.GlobalCallID = B.GlobalCallID
> and a.CallNumber = b.CallNumber)
> You can use as many fields as you need to ensure uniqueness.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "sketchy" <sketchy@.discussions.microsoft.com> wrote in message
> news:CCA5542F-5C15-413A-8B8F-D2200C1BEAD3@.microsoft.com...
> > Hi Roy,
> >
> > Thank you SO MUCH for your quick response.
> >
> > 1. Yes, only new data needs to be added. No data needs to be refreshed,
> > so
> > that's good.
> >
> > 2. One little wrinkle in the plan is that much to my dismay, it does
> > appear
> > that the "GlobalCallID" field isn't necessarily a unique number, but I do
> > have a field adjacent to it ("CallNumber") where no records would ever
> > have
> > the same combination of the two. How would you ammend your last statement
> > to
> > accomodate for this? (Ugh, I know... that probably doesn't make things
> > simpler)
> > --
> > Sketchy
> >
> >
> > "Roy Harvey" wrote:
> >
> >> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
> >> <sketchy@.discussions.microsoft.com> wrote:
> >>
> >> >Okay, so here is what I have done. (this is a SQL 2005 DB by the
> >> >way...)
> >> >
> >> >1. I have my original table, which will be used for my reporting needs,
> >> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
> >> >"Field3" etc. but the main field that has the unique info in it is
> >> >called
> >> >"GlobalCallID"
> >> >
> >> >2. I have created a new table, which will be used for my staging of
> >> >data,
> >> >called 'phonelogstaging'. This database has the EXACT same field names
> >> >and
> >> >types.
> >> >
> >> >3. I've set up SSIS to import my log files into the staging table
> >> >("phonelogstaging"). It wipes out any previous data in this staging
> >> >table,
> >> >so there is no possibility of duplicates in this table. Everything is
> >> >working good there.
> >> >
> >> >Both tables have a field called GlobalCallID that has the unique number
> >> >in
> >> >it that I should be able to check against.
> >> >
> >> >So considering the above, how would my statement look?
> >>
> >> Assuming that data already in the table does not need to be refreshed,
> >> only new data added:
> >>
> >> INSERT phones
> >> SELECT <column list>
> >> FROM phonelogstaging as A
> >> WHERE NOT EXISTS
> >> (SELECT * FROM phones as B
> >> WHERE A.GlobalCallID = B.GlobalCallID)
> >>
> >> If the incoming data itself has duplicates, add DISTINCT after the
> >> word SELECT.
> >>
> >> If you need to refresh the rest of the columns of matching rows from
> >> the staging data, you would also run the following BEFORE the command
> >> above.
> >>
> >> UPDATE phones
> >> SET col1 = A.col1,
> >> col2 = A.col2
> >> FROM phonelogstaging as A
> >> WHERE phones.GlobalCallID = A.GlobalCallID
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>
>

Remove Duplicate

HI All,
I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..
Thnx
;)
ShaniOriginally posted by shani
HI All,

I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..

Thnx
;)
Shani

http://www.cleardata.biz/articles/dupes.aspx
http://www.sqlteam.com/item.asp?ItemID=3331|||Originally posted by shani
HI All,

I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..

Thnx
;)
Shani

read this:
http://www.databasejournal.com/features/mssql/article.php/2235081

Remove default value from a table

Hi there.
I cannot figure this out, please help.
I have a table with an attribute with a default value. How do I erase this default value (with sql). I do not want to lose any data in my table.I didn't get that to work, it said I should use ALTER TABLE DROP CONSTRAINT. I have tried that before but without success.

But after a few minutes (almost an hour) I found a way to solve it:

ALTER TABLE voucher DROP CONSTRAINT DF__voucher__voucher__56D3D912;

The Constraint name I found by looking in the sysobjects table.

Thanks anyway!|||just do an alter table ... alter column ... and omit the default.

Remove Cursor

Hi all,
I have a table A with 2 cols one id and the other linkedids separated with
pipe "|"
id linked ids
1 4|5|6
2 9|10|11
I want the output as without using cursors or while loop..
1 4
1 5
1 6
2 9
2 10
2 11
Your thoughts on this...
Thanks in advance,
PradeepSee this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan|||And my personal favourite:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
ML|||This chy little solution can deal with up to four items ( three pipes ):
SET NOCOUNT ON
DROP TABLE #linked_ids
CREATE TABLE #linked_ids ( id INT PRIMARY KEY, linked_ids VARCHAR( 50 ) )
INSERT INTO #linked_ids
SELECT 1, '4|5|6|7' UNION
SELECT 2, '9|10|11|12' UNION
SELECT 3, '13|14|15|16|17'
SET NOCOUNT OFF
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 1 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 2 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 3 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 4)
FROM #linked_ids t
Damien
"Pradeep Kutty" wrote:

> Hi all,
> I have a table A with 2 cols one id and the other linkedids separated with
> pipe "|"
> id linked ids
> 1 4|5|6
> 2 9|10|11
> I want the output as without using cursors or while loop..
> 1 4
> 1 5
> 1 6
> 2 9
> 2 10
> 2 11
> Your thoughts on this...
> Thanks in advance,
> Pradeep
>
>|||See: http://tinyurl.com/b3ce2
Anith

Remove Comma from rows in table

Hello,
My schema is as follows:
Create table Test (Emailaddress varchar(50))
I have numerous rows that have a comma after the emailaddress and I need to
remove the comma. Example
jonsamules@.aol.com,
Any help would be great.Look up the REPLACE function in the Books Online.
Lontae Jones wrote:
> Hello,
> My schema is as follows:
> Create table Test (Emailaddress varchar(50))
> I have numerous rows that have a comma after the emailaddress and I need t
o
> remove the comma. Example
> jonsamules@.aol.com,
> Any help would be great.|||"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:ABB60657-A5B5-4CB6-9FDE-3CCF2C4BCA47@.microsoft.com...
> Hello,
> My schema is as follows:
> Create table Test (Emailaddress varchar(50))
> I have numerous rows that have a comma after the emailaddress and I need
> to
> remove the comma. Example
> jonsamules@.aol.com,
> Any help would be great.
Try the REPLACE function.
UPDATE Test
SET Emailaddress = REPLACE(Emailaddress, ',' ,'')
Rick Sawtell
MCT, MCSD, MCDBA

remove columns created by replication process

One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :

select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'

Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :

...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?

Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.

Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!

MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?

Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.

-PatP

remove chr(10) char with Tsql?

Hello,
I am writing data to a table using a DTS package from a VB
app. One of the columns, nvarchar column, is getting
carriage/return char appended to the data.
Select '*' + fld1 + '*' From tbl1
fld1 contains digits. I get this
* 10*
If I do this:
Declare @.s varchar(10)
Select @.s = fld1 From tbl1
Print '*' + @.s + '*'
I get this:
*
10*
If I say Update tbl1 Set fld1 = Ltrim(fld1)
I still get * 10*. But if I say Update tbl1 Set fld1 = 10
Now I get *10*
How can I remove these chr(10) chars with Tsql? Should I
loop through a cursor and use Substring?
declare @.s varchar(20)
declare @.t varchar(20)
select @.s = cn from tblincidentsn where rownum = 1
print '*' + @.s + '*'
set @.t = substring(@.s,3, len(@.s) - 2)
print ''
print '*' + @.t + '*'
gives me
*
02*
*02* <-- correct format
Thanks in advance for any suggestions.
RonAre you sure you don't want both CHAR(13) and CHAR(10) (CR and LF,
respectively)?
SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||SET @.s = REPLACE(REPLACE(@.s, CHAR(10), ''), CHAR(13), '')
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||> SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
I usually replace them separately, for two reasons:
(a) not all programs behave well. Some only include 10, some only include
13.
(b) not all programs behave well. Many include these in the wrong order.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePxgmo2IFHA.3484@.TK2MSFTNGP12.phx.gbl...
> (a) not all programs behave well. Some only include 10, some only include
> 13.
> (b) not all programs behave well. Many include these in the wrong order.
Both are excellent points!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

remove character from string

Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
Paul G
Software engineer.
Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||It worked! thanks.
Paul G
Software engineer.
"Plamen Ratchev" wrote:

> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
sql