Remove Identity seed w/ T-SQL

Does anyone know how to remove the Identity attribute of a column using T-SQL. I have created the Identity attribute with the following. I know I can do it with a script created through Enterprise Manager (which is really long and hard to re-create with Dynamic SQL), but I am looking for a one line opposite to the following statement:

IF (SELECT COL_LENGTH('[dbo].tablename', 'UserID')) IS NULL
ALTER TABLE [dbo].tablename ADD UserID INT NOT NULL IDENTITY (1, 1)

something like:
ALTER TABLE [dbo].tablename ALTER COLUMN UserID"REMOVE IDENTITY"

Thanks,
JoshThere is not a simple way to accomplish this. You need basically to drop the column altogether and then add it back to the table. as a non-identity column.

So you could add a new non-identity column to the table (UserIDNEW), copy your UserID data into it, drop the UserID column, then rename your UserIDNEW column back to UserID.

Wish I had better news.

I looked upthis article to explain more concretely what I am talking about.

Terri|||Terri,
I too found the same article. I didn't think there was a simple way to do it, but I thought I would ask.

Thanks,
Josh

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 double value

Hy
Never use/practice SQL a lot, (vb... more, have free msde 2000) .
2 questions
A)is it simple to write a T-SQL query for having 2) at result starting
from 1) .
B)how to test dynamically sql with parmaeter ( using vb ADO)

1) before query
columA columB
d e <-same
e d <-same
e e
e d <-same

2)after query
columA columB
d e or e d
e e> A)is it simple to write a T-SQL query for having 2) at result starting
> from 1) .

SELECT DISTINCT
COALESCE(S2.a,S1.a) AS a,
COALESCE(S2.b,S1.b) AS b
FROM Sometable AS S1
LEFT JOIN Sometable AS S2
ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b

> B)how to test dynamically sql with parmaeter ( using vb ADO)

This may help:
http://msdn.microsoft.com/library/e...oprg02_6df7.asp
See also:
http://www.sommarskog.se/dyn-search.html

--
David Portas
----
Please reply only to the newsgroup
--|||David Portas wrote:
>>A)is it simple to write a T-SQL query for having 2) at result starting
>>from 1) .
>
> SELECT DISTINCT
> COALESCE(S2.a,S1.a) AS a,
> COALESCE(S2.b,S1.b) AS b
> FROM Sometable AS S1
> LEFT JOIN Sometable AS S2
> ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b
>
>>B)how to test dynamically sql with parmaeter ( using vb ADO)
>
> This may help:
> http://msdn.microsoft.com/library/e...oprg02_6df7.asp
> See also:
> http://www.sommarskog.se/dyn-search.html

hy
thank a lot for sharing your knowledge.
perhaps it was easy for you buit very hard for me
thank's for your quicky response

remove all double quotes from column values using t-sql

Hi,
I am having problem withj double quotes being inserted automatically when i
am inserting data using a CSV file.I am using a C# program to insert the
values.
My coulmn is called whereClause and it is a varchar(50) e.g
'ISNULL(salary,2000)=2000'
but what gets inserted is "'ISNULL(salary,2000)=2000'"
I am using this value in dynamic sql to generate a query such as
exec 'select salary from employee where '+@.whereClause and I get error
because of the automatic double quote insertion.
The CSV file does not have double quotes so I need a solution that will do
either 1 of the following
1)suppress insertion of double quotes in the table in C# OR
2)write query to remove the double quotes using T-SQL
Solution 2 is preferable.
Your help is highly appreciated.
Thanks.if I understood the question correctly, this should work..
declare @.a varchar(30)
set @.a = '"ISNULL(salary,2000)=2000"'
select replace(@.a,'"','')|||Thans for the reply.
But the problem is I have about a thousand rows and each condition is
different and having double quotes eg
id whereClause
1 "ISNULL(salary,5000)=5000"
2 "ISNULL(bonus,400)>600"
3 "IN(600,40)"
I want to remove all double quotes and update the table with proper values i
e
if table contains "ISNULL(salary,5000)=5000" I want to update it as
ISNULL(salary,5000)=5000
Thanks.
"Omnibuzz" wrote:

> if I understood the question correctly, this should work..
> declare @.a varchar(30)
> set @.a = '"ISNULL(salary,2000)=2000"'
> select replace(@.a,'"','')|||Use REPLACE:
create table #x (s varchar(30))
INSERT #x values ('abcdefg')
INSERT #x values ('"hijklmn"')
INSERT #x values ('o"p"q"r"s"t"u')
select * from #x
UPDATE #x
SET S = REPLACE(S,'"','')
select * from #x
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 11:18:02 -0700, tech77
<tech77@.discussions.microsoft.com> wrote:

>Hi,
>I am having problem withj double quotes being inserted automatically when i
>am inserting data using a CSV file.I am using a C# program to insert the
>values.
>My coulmn is called whereClause and it is a varchar(50) e.g
>'ISNULL(salary,2000)=2000'
>but what gets inserted is "'ISNULL(salary,2000)=2000'"
>I am using this value in dynamic sql to generate a query such as
>exec 'select salary from employee where '+@.whereClause and I get error
>because of the automatic double quote insertion.
>The CSV file does not have double quotes so I need a solution that will do
>either 1 of the following
>1)suppress insertion of double quotes in the table in C# OR
>2)write query to remove the double quotes using T-SQL
>Solution 2 is preferable.
>Your help is highly appreciated.
>Thanks.|||Thanks.That worked like a charm!!
"Roy Harvey" wrote:

> Use REPLACE:
> create table #x (s varchar(30))
> INSERT #x values ('abcdefg')
> INSERT #x values ('"hijklmn"')
> INSERT #x values ('o"p"q"r"s"t"u')
> select * from #x
> UPDATE #x
> SET S = REPLACE(S,'"','')
> select * from #x
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 11:18:02 -0700, tech77
> <tech77@.discussions.microsoft.com> wrote:
>
>sql