If not CURSORS ?

I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.

Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@.here.now) with the next sequential value (i.e. smith.j02@.here.now)

Any takers?

Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.Concatenate the datetime(getdate()) down to 1/1000 second. I am sure it will be unique. That's most of the spam mailers do when they create a fake ID to get around your blocking.|||Better yet, concatenate newid(). That will guarantee you a unique value all the time.|||Originally posted by joejcheng
Better yet, concatenate newid(). That will guarantee you a unique value all the time.

It also has to be sequential, not just unique|||You can use substring and max functions to achieve the same sequentially.|||You can't do this with sequential values if you insist on the stipulation that a record can be removed from the database and readded at another time. Even using a cursor to find out that smith.j02@.here.now, smith.j03@.here.now, and smith.j04@.here.now already exist, there is simply no way to know whether smith.j05@.here.now was not previously created and deleted. You have to store the used values permanently somewhere.|||...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...|||Unfortunately it is possible that more than one new entry can be made in the same import. (i.e. smith.j exists and two more smith.j are imported).

The list of historic values are being saved in a seperate table as described without the "EMailIncrement int" field. I had considered your exact solution but did not know how to increment 2 newly added values with different increments.

Originally posted by blindman
...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...

Unfortunate|||Use a cursor in combination with the table of historical values.

If MSSQL says successfully backed up... should be fine.... ?

I have created the database maintenance plan for SQL Server for full backup,
differential backup and transactional backup and scheduled. If I see the job
status as completed successfully and if I can see the physically the file
being created, am I on the safe side of thinking that I have the recoverable
backup. I normally check the restore activity once in 6 months. Do I have to
check any thing else to make sure my back up is successful? Does any one has
any experience of not being able to restore the SQL Server instances from
the backup copies.
I am making sure that I am perfect in terms of backup and restore
activities.
SQL2K.
Thank you,
AllenAllenHubatka (AllenHubtka_67@.hotmail.com) writes:
> I have created the database maintenance plan for SQL Server for full
> backup, differential backup and transactional backup and scheduled. If I
> see the job status as completed successfully and if I can see the
> physically the file being created, am I on the safe side of thinking
> that I have the recoverable backup. I normally check the restore
> activity once in 6 months. Do I have to check any thing else to make
> sure my back up is successful? Does any one has any experience of not
> being able to restore the SQL Server instances from the backup copies.
There is really only one way to check if a backup is good, and that
is to restore it and run DBCC CHECKDB on it. Whether you always do this,
or only at random occassions depends on how paranoid you are. Personally,
I'd say twice a year is a tad seldom.
Not that backups fail that often, but then again maybe you have some
creeping corruption. I have experienced that DBCC on a restored file
revealed errors that did not exhibit in the original database. This
was in SQL 6.0, so this may not happen today. Then again, it's all a
matter of how paranoid you are...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

IDENTITY_INSERT is set to OFF

I am trying to insert a new record to a table in my application created by VWD Express. I get beack the responce "Cannot insert explicit value for identity column in table 'Tradersa' when IDENTITY_INSERT is set to OFF" . I have a key record in the table which I would like to increment automatically as I add records so I have set the is identity value to true and both the identity seed and increment to 1.

I have done a fair bit or searching but do not know how to set the table value of IDENTITY_INSERT to ON. Is this as the table is set up or as the record is about to be added? I beleive I should set this when I add the record, but do not know how to in VWD.

Any help would be most welcome. Many thanks in advance

Looks like you are trying to insert a value into a column that has been defined as IDENTITY column? Is that right?|||You need to change the Identity Insert mode. Have a read ofthis article it should explain what is going on.|||

Yes the column is set as IDENTITY. After a bit more reading I think that the issue is with the explicit naming of the identity column. I don't believe that I am explicity defining the field just as @.Trader_ID.

|||Thanks for this, I would like to insert the field without speciying it so that it will increment automatically. The solution in the doc seems to specify the record to be added to the identity field.

Identity range when rows already exist

I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:

> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.

>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place

Identity or unique identifier

Hi there,
I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.
Any help would be great
Cheers
StuFor Identity columns you need to set the IsIdentity property to "Yes" under Identity Specification in the design view of the table. You then set the Identity seed which is the increment you want to have for each value (usually 1).
For Uniqueidentifiers ( I have not used them until now though I have worked on systems that have used them) you could use the NEWID() function.
Check out books on line for more info. Both have performance/efficiency issues that you need to understand before you implement them.|||Thats brill thanks very much for your response.
All the best
Stu

Identity columns problem in replication among n no of subscribers

I have created Transaction Replication.
Nearly 100's of tables there in my database.
I have created Recordid (identity) column with primary key in each table and
all are incremented by 1.
Front End application in VB6 is 100% dependable on this column. Changes in
this column may spoil my work of VB6.
Synchronisation is done @. subscribers side using below activex component in
VB6
Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
Subscribers side does not show identity columns with primary key. It shows
only name of the column as INT.
Front application is failed to run @. subscribers side.
Pls suggest how to Replicate identity column among n no. of subscribers.
Best Regards
Sanjay
You will have to create different identity seeds and an increments of 2 on
both sides - on the subscriber a seed of 2 with an increment of 2, and on
the publisher an seed of 1 with an increment of 1.
Make sure the identity column has the not for replication property on it.
Then set your articles up to not modify the existing tables on the
subscriber and in your pre-snapshot script have your table creation scripts
(along with their respective indexes).
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>I have created Transaction Replication.
> Nearly 100's of tables there in my database.
> I have created Recordid (identity) column with primary key in each table
> and all are incremented by 1.
> Front End application in VB6 is 100% dependable on this column. Changes in
> this column may spoil my work of VB6.
> Synchronisation is done @. subscribers side using below activex component
> in VB6
> Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
> Subscribers side does not show identity columns with primary key. It shows
> only name of the column as INT.
> Front application is failed to run @. subscribers side.
> Pls suggest how to Replicate identity column among n no. of subscribers.
> Best Regards
> Sanjay
>
>
>
>
>
|||Dear Friend Hilary
Thanks for the help.
But one more solution can be there if I give different IDENTITY ranges to
each subscribers.
Pls help me If you know how to give diff IDENTITY ranges to each
subscriber.
Thanks in Advance
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
> You will have to create different identity seeds and an increments of 2 on
> both sides - on the subscriber a seed of 2 with an increment of 2, and on
> the publisher an seed of 1 with an increment of 1.
> Make sure the identity column has the not for replication property on it.
> Then set your articles up to not modify the existing tables on the
> subscriber and in your pre-snapshot script have your table creation
> scripts (along with their respective indexes).
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>
|||You certainly can, so you can go to your subscriber and do a
dbcc('mytablename', checkident, reseed, 10000000) and it will probably work.
However you will always have to monitor the range you assigned and adjust it
as necessary.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Friend Hilary
> Thanks for the help.
> But one more solution can be there if I give different IDENTITY ranges to
> each subscribers.
> Pls help me If you know how to give diff IDENTITY ranges to each
> subscriber.
> Thanks in Advance
> Best Regards
> Sanjay
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
>
|||Why do you need the identity property on the subscribers if you have
transactional replication, where the subscribers are treated as RO?
Perhaps this is being used as a failover server? If this is the case, the
easiest way to set it up is to enable automatic identity range management,
large range sizes and queued updating subscribers.
This way the subscriber can start entering data once the publisher is down
without any meddling with identity ranges on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear friend Hilary
Thanks for your full co-operation. My all doubts are clear now except below
2.
1. If I am having file called EMP with identity column Recid.
I want to give automatic range control on publisher for n no. subscribers
what will be the command ?
2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
able to store or pull others data range from 1 to 1000 or 2001 to 3000.
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
> You certainly can, so you can go to your subscriber and do a
> dbcc('mytablename', checkident, reseed, 10000000) and it will probably
> work. However you will always have to monitor the range you assigned and
> adjust it as necessary.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
>
|||For automatic identity range management on the publisher for queued and
merge replication you have to drop your publication and subscriptions and
enable this feature - in the articles tab of the create publication wizard.
A subscriber with a range of 1000-2000 will be able to receive replicated
commands for other ranges if the not for replication constraint is enabled.
Otherwise he will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%239Zlc%23yBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear friend Hilary
> Thanks for your full co-operation. My all doubts are clear now except
> below 2.
> 1. If I am having file called EMP with identity column Recid.
> I want to give automatic range control on publisher for n no. subscribers
> what will be the command ?
> 2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
> able to store or pull others data range from 1 to 1000 or 2001 to 3000.
> Best Regards
> Sanjay
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
>

IDENTITY Column!

Hi all,
I have noticed when i export data, that any resulting tables created don't
seem to include the IDENTITY attribute on the necessary columns.
These attributes are defined in the source tables, but don't carry over to
tables resulting from an export.
Can someone fill me in on what is happening there?
Is there something i could be missing?
Cheers,
AdamAdam
create table source
(
col1 int identity(1,1) not null primary key,
col2 char(1)
)
insert into source (col2) values ('a')
--create a destination table
select * into destination from source
--you will see that an identity property exists but primary key constraint
doesn't
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:exS9CWHAGHA.3372@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have noticed when i export data, that any resulting tables created don't
> seem to include the IDENTITY attribute on the necessary columns.
> These attributes are defined in the source tables, but don't carry over to
> tables resulting from an export.
> Can someone fill me in on what is happening there?
> Is there something i could be missing?
> Cheers,
> Adam
>|||By which method do you export data?|||By which method do you export data?

Identity column without script?

I have a database that is already created and I would like to change all the row id columns (which are currently bigint fields) and turn them into autonumbering Identity fields. Is there a way to do this through the enterprise manager or do I need to recreate all tables in the database using a script that creates identity columns in CREATE_TABLE and then import existing data into it?

Thanks in advance!USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 bigint, col2 char(2))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
GO

-- This is what EM will Do

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_myTable99
(
Col1 int NOT NULL IDENTITY (1, 1),
col2 char(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, col2)
SELECT CONVERT(int, Col1), col2 FROM dbo.myTable99 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
COMMIT
GO

SELECT * FROM myTable99
GO

sp_help myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Yes, but I wanted to do it through the enterprise manager (the database is already set up and populated, but not in production yet.). I was hoping maybe all I had to do was enter a formula in the design view for each table, and then do inserts in code without having to enter that field in my inserts.

Can this be done?|||if you are asking if you can add an identity to a column after the table has been created and the data exists, the answer is yes

open the enterprise manager
right click the table that you want to modify
Right click the table and select Design Table
in the grid at the top choose the column you want to add the identity to
and at the bottom select the following properties

identity = yes
seed = this is the initial value which in the case of existing data is the highest value placed in the column. os for example if your last value in the col was 234 the identityseed would be 234
increment the number that you want to increase the identity by. usually 1

when you insert another row to this table the identity will add 1 to the seed and give you 235 as your next value.

is this what you wanted? :eek:|||That's exactly what I wanted! Thanks!!

Identity Column

I have created an identity column in a table. Now I want to drop the
identity property of the column throug Alter Table Alter column statement.
Can anybody tell How?
Thanking you
Toeen> I have created an identity column in a table. Now I want to drop the
> identity property of the column throug Alter Table Alter column statement.
> Can anybody tell How?
You can't. 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.

Identity column

Hi all,
Does anyone know if you can put an identity column into a table that
has been created by SELECT...INTO query, either at the same time as
your creating it or after?
Thanks alot!
RobIDENTITY() function.
"robken" <robin.kennedy@.hotmail.co.uk> wrote in message
news:1155311004.583704.305210@.h48g2000cwc.googlegroups.com...
> Hi all,
> Does anyone know if you can put an identity column into a table that
> has been created by SELECT...INTO query, either at the same time as
> your creating it or after?
> Thanks alot!
> Rob
>|||Sure - take a look at the IDENTITY function in BOL:
USE pubs
go
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||And you might as well want to have a look at the OUTPUT clause, which is
new in SQL Server 2005.
"Mike C#" <xyz@.xyz.com> wrote in news:OWo1Y5VvGHA.356@.TK2MSFTNGP04.phx.gbl:
> IDENTITY() function.
> "robken" <robin.kennedy@.hotmail.co.uk> wrote in message
> news:1155311004.583704.305210@.h48g2000cwc.googlegroups.com...
>> Hi all,
>> Does anyone know if you can put an identity column into a table that
>> has been created by SELECT...INTO query, either at the same time as
>> your creating it or after?
>> Thanks alot!
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Hi,
Looks like your question is to have the identity property available while
you do a SELECT * INTO or you need to use ALTER Table to
enable identity correct?
If your source table has an IDENTTY then automatically identity column by
itself. Incase if you need to add a new column to
your exiting table you can use below code;-
alter table newtable add colnew int identity(1,1)
Thanks
hari
SQL Server MVP
"robken" <robin.kennedy@.hotmail.co.uk> wrote in message
news:1155311004.583704.305210@.h48g2000cwc.googlegroups.com...
> Hi all,
> Does anyone know if you can put an identity column into a table that
> has been created by SELECT...INTO query, either at the same time as
> your creating it or after?
> Thanks alot!
> Rob
>

Identity column

Hi all,
Does anyone know if you can put an identity column into a table that
has been created by SELECT...INTO query, either at the same time as
your creating it or after?
Thanks alot!
RobIDENTITY() function.
"robken" <robin.kennedy@.hotmail.co.uk> wrote in message
news:1155311004.583704.305210@.h48g2000cwc.googlegroups.com...
> Hi all,
> Does anyone know if you can put an identity column into a table that
> has been created by SELECT...INTO query, either at the same time as
> your creating it or after?
> Thanks alot!
> Rob
>|||Sure - take a look at the IDENTITY function in BOL:
USE pubs
go
SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||And you might as well want to have a look at the OUTPUT clause, which is
new in SQL Server 2005.
"Mike C#" <xyz@.xyz.com> wrote in news:OWo1Y5VvGHA.356@.TK2MSFTNGP04.phx.gbl:
[vbcol=seagreen]
> IDENTITY() function.
> "robken" <robin.kennedy@.hotmail.co.uk> wrote in message
> news:1155311004.583704.305210@.h48g2000cwc.googlegroups.com...
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Hi,
Looks like your question is to have the identity property available while
you do a SELECT * INTO or you need to use ALTER Table to
enable identity correct?
If your source table has an IDENTTY then automatically identity column by
itself. Incase if you need to add a new column to
your exiting table you can use below code;-
alter table newtable add colnew int identity(1,1)
Thanks
hari
SQL Server MVP
"robken" <robin.kennedy@.hotmail.co.uk> wrote in message
news:1155311004.583704.305210@.h48g2000cwc.googlegroups.com...
> Hi all,
> Does anyone know if you can put an identity column into a table that
> has been created by SELECT...INTO query, either at the same time as
> your creating it or after?
> Thanks alot!
> Rob
>

Identity Column

I have created an identity column in a table. Now I want to drop the
identity property of the column throug Alter Table Alter column statement.
Can anybody tell How?
Thanking you
Toeen> I have created an identity column in a table. Now I want to drop the
> identity property of the column throug Alter Table Alter column statement.
> Can anybody tell How?
You can't. 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.