experiment with Identity 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 2005 Forums

Transact-SQL (2005)

experiment with Identity Column

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 13:32:25

Maybe no one thought of this before or they did.

I am just playing with this

I create a table with one column only and that column is identity type.

create table test(C1 int identity(1,10))

now i want to do inserts in this and i want the inserts to be automated not by setting identity insert ON

insert into test values () and everytime i run my insert the data should be added to this table.

is this possible. I know its not required but
IDEAL MIND IS DEVILS WORKSHOP and this is what i was thinking about during my lunch time.

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 15:06:13

No body replying to this.

need to insert data without setting identity_insert to ON

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 15:51:28

Wow No Replies. Is it that dumb?

Ashley Rhodes

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-16 : 15:54:52

No it is not possible.

What excatly are you trying to accomplish?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 15:59:22

nothing much just curious.

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-16 : 16:10:18

>>> now i want to do inserts in this and i want the inserts to be automated not by setting identity insert ON
So, You cannot insert into the identity column

>>>insert into test values () and everytime i run my insert the data should be added to this table.
What data will you insert and into which column?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-16 : 16:10:33

you can do this


CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

SET NOCOUNT ON
DECLARE @x int

SET @x = 0

WHILE @x < 100
  BEGIN
	INSERT INTO myTable99(Col2) SELECT 'a'
	SELECT @x = @x + 1
  END

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-16 : 18:58:17

[code]CREATE TABLE MyTemp (ID INT IDENTITY(1,1))

SET IDENTITY_INSERT MyTemp ON

INSERT MyTemp (ID) VALUES(1)

SET IDENTITY_INSERT MyTemp OFF

SELECT * FROM MyTemp

DROP TABLE MyTemp
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources