Identity_Insert OFF

I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
What i should do ?
thank you
This is sample code pasted from SQL Books Online from the SET
IDENTITY_INSERT property:
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO
You can find the answers to almost every question about syntax in BOL.
HTH,
Mary
On Wed, 14 Apr 2004 23:06:03 -0700, George
<anonymous@.discussions.microsoft.com> wrote:

>I try to insert values to a field (which is a bigint identity(1 ,1) primary key) and i take message that Identity_Insert is OFF
>What i should do ?
>thank you

IDENTITY on a BitMap column?

Consider the following table
CREATE TABLE Attributes( id int identity(1,1),
name varchar(100),
mask bigint)
Now, attribute mask will be a bit mask of the attribute name. Consider the
data:
id name mask
-- -- --
1 foo 1
2 bar 2
3 mann 4
4 frau 8
5 kein 16
6 alles 32
How do I put a trigger on this table, or something so that I do not have to
worry about the mask when I insert data? Inserting 1 record at a time would
be no problem, just get the MAX of mask and double it. But how to handle
two or more inserts at a time? Should I use an instead-of-trigger?
Sorry about the poor english.
Dieter"Dieter Katzenland" <deiter@.rrtc.com> wrote in
news:#e8DYffjDHA.2964@.tk2msftngp13.phx.gbl:
> How do I put a trigger on this table, or something so that I do not
> have to worry about the mask when I insert data? Inserting 1 record
> at a time would be no problem, just get the MAX of mask and double it.
> But how to handle two or more inserts at a time? Should I use an
> instead-of-trigger?
hi,
in this case it would be enough to let the mask empty on inserting and then
use an AFTER INSERT Trigger for calculating the mask.
--
best regards
Peter Koen
--
MCAD, CAI/R, CAI/S, CASE/RS, CAT/RS
http://www.kema.at|||Assuming that your multi-row INSERT originates from a table or query:
CREATE TABLE foo (name VARCHAR(10) PRIMARY KEY)
INSERT INTO foo VALUES ('Alpha')
INSERT INTO foo VALUES ('Beta')
INSERT INTO Attributes (id, name, mask)
SELECT COUNT(*)+
(SELECT MAX(id) FROM attributes),
A.name,
POWER(2,COUNT(*))*
(SELECT MAX(mask) FROM attributes)
FROM foo AS A
JOIN foo AS B
ON A.name >= B.name
GROUP BY A.name
--
David Portas
--
Please reply only to the newsgroup
--

Identity column: what happens when it runs out?

I have a table containing an identity column (bigint) as its primary key.
The table will have very frequent insertions/deletions from it.
Many items will be added, but they aren't really expected to be there for
long. I can never expect that the table is empty, however.
So, eventually, the new ID's that get added will increment up to the maximum
bigint value. What happens then? Does it automatically wrap and start
over?
If it starts over, how does it handle any values that may still exist?
How should I handle this?
Thanks!
--
Adam Clauss
cabadam@.tamu.eduYou'll receive an overflow error if IDENTITY reaches the upper bound of the
datatype. Is that likely with a BIGINT? Assuming you start at zero then even
if you generated 1 billion rows per second, 24x7 it would still take nearly
300 years before you hit the ceiling. Your hardware will fall apart rather
sooner! If you're still not convinced then there's always NUMERIC instead.
David Portas
SQL Server MVP
--|||Hmm.. ok, I hadn't realized that bigint ran quite THAT big...
Thanks!
--
Adam Clauss
cabadam@.tamu.edu
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Y7qdnZR3Gc1nVG_cRVn-oA@.giganews.com...
> You'll receive an overflow error if IDENTITY reaches the upper bound of
> the datatype. Is that likely with a BIGINT? Assuming you start at zero
> then even if you generated 1 billion rows per second, 24x7 it would still
> take nearly 300 years before you hit the ceiling. Your hardware will fall
> apart rather sooner! If you're still not convinced then there's always
> NUMERIC instead.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have a table containing an identity column (bigint) as its primary key.
> The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
Do you really need an IDENTITY column?

> So, eventually, the new ID's that get added will increment up to the
maximum
> bigint value. What happens then? Does it automatically wrap and start
> over?
No, you will get an overflow error.

> How should I handle this?
Not having an IDENTITY column?
A|||In that case maybe you don't need it. INTEGER is half the size of BIGINT and
still can store values from -2,147,483,648 to 2,147,483,647.
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OdRGx9MAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> Do you really need an IDENTITY column?
What else would I use? No other column would necessarily be unique.
Adam Clauss
cabadam@.tamu.edu|||> > Do you really need an IDENTITY column?
> What else would I use? No other column would necessarily be unique.
Oh great, another "I don't have a key so I'll make one up"... are you saying
that you could have multiple rows with the exact same data in every column?
What exactly are you trying to model?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgwuDLPAFHA.2804@.TK2MSFTNGP15.phx.gbl...
> Oh great, another "I don't have a key so I'll make one up"
You made MVP with that kind of an attitude? If this is the help you are
offering, I don't want any.
... are you saying
> that you could have multiple rows with the exact same data in every
> column?
That would be what I just said. Like I said though, if that's the kind of
help you are offering, thanks, but no thanks.
Adam Clauss
cabadam@.tamu.edu|||> That would be what I just said. Like I said though, if that's the kind of
> help you are offering, thanks, but no thanks.
Great, have fun. Maybe you'll get lucky and Celko won't stumble across this
one.|||If you are concerned about about running out of IDENTITY values with a
"numeric/int" based datatype, I would suggest you to look at the
UNIQUEIDENTIFIER datatype.
I am a VERY VERY VERY firm believer in the UNIQUEIDENTIFIER datatype (aka
the GUID [Globally Unique Identifier] in .NET and elsewhere). I resisted it
at first... but came around to understand it and what it could do for my
applications...
It will take a bit change on your part to convert to the GUID way of
thinking, but it might be worth it for you. I know it was for me but well
worth it!!!
Chris
"Adam Clauss" <cabadam@.nospam.tamu.edu> wrote in message
news:uKir3hMAFHA.2600@.TK2MSFTNGP09.phx.gbl...
>I have a table containing an identity column (bigint) as its primary key.
>The table will have very frequent insertions/deletions from it.
> Many items will be added, but they aren't really expected to be there for
> long. I can never expect that the table is empty, however.
> So, eventually, the new ID's that get added will increment up to the
> maximum bigint value. What happens then? Does it automatically wrap and
> start over?
> If it starts over, how does it handle any values that may still exist?
> How should I handle this?
> Thanks!
> --
> Adam Clauss
> cabadam@.tamu.edu
>

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!!