Insert value into autonumber column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert value into autonumber column

Author

Topic

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-04-12 : 04:55:39

how to insert a value to a autonumber column?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-04-12 : 05:00:43

Look up "SET IDENTITY_INSERT" in BOL.

Here is a sample from BOL:

This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.


-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products 
WHERE product = 'saw'
GO

SELECT * 
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- 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

SELECT * 
FROM products
GO
-- Drop products table.
DROP TABLE products
GO

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-04-12 : 06:06:49

try this..

set identity_insert <tablename> on
insert into <tablename> values(<colname>...)
set identity_insert <tablename> off

satish.r
"Way to success is always under Construction"

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-04-12 : 06:08:09

i got 2 primary key in the database, one is the autonumber column another is the ID column... but it seem the data in the primary key column can duplicate....

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-04-12 : 07:31:51

Confusing...
Sample records please...

satish.r
"Way to success is always under Construction"

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-04-12 : 20:22:10

example i got 3 columns... index, ID, col_X... at first i set my index to autonumber and the ID to primary key... my record data as follow
index ID col_x
3 A123 xx
4 B234 xxx
1 C345 xx
2 D456 xxx

my ID column is arrange ascending but my index column is not, suppose last record enter will place at the bottom but is seem index-4 is place on the second row.

this is my desire record placement
index ID col_x
1 C345 xx
2 D456 xxx
3 A123 xx
4 B234 xxx

how my sql insert statement should look like... or did i set anything wrong in my database??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-12 : 21:21:13

records are not stored in the database in any particular order. You don't have to worry about how SQL Server store your records. The important thing is when you retrieve the records, how do you want it to order ?

You should use ORDER BY to define the sequence how the records are retrieved back.
example :

select * 
from   yourtable
order by index

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-04-12 : 23:05:05

i'm using access as my database and use vb to control so in the dtaabase i saw the particular oder appear...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-13 : 00:59:50

If you are displaying the data on the grid or taking them in the recordset, just while retreving the data use order by clause what Khtan gave you ..

It should work fine ..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

lsy
Yak Posting Veteran

57 Posts

Posted - 2006-04-13 : 02:08:20

i know that work but i want when open database manually it look more organise...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources