help with auto-increment (possibly via identity) 1

[collierd]

Hello

Does anybody know what is wrong with this:

   insert into PAP03_TEST.dbo.holdslines
      select identity(int,1,1), substring(sa.holdslinesid, 2, 20)      from sanalysis sa

It comes back with Incorrect syntax near ','.

The error lies with the identity(int,1,1) part
Is this part right?

Any thoughts how to correct it, am I misusing identity?
This field should just auto-increment

Thanks

damian.

 

[SQLDenis]

identity() can only be used with a select into statement

what are you trying to accomplish?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

From BOL:
Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Why just not add an identity column in holdslines table, then isert will looks like this:

 insert into PAP03_TEST.dbo.holdslines
 select substring(sa.holdslinesid, 2, 20)
        from sanalysis sa

Borislav Borissov

 

[collierd]

ok, thanks

Was trying to just simply have a unique auto-generated field.

Just missing the assignment of an identity field

I will need to periodically flush this and start from 1 again but I guess I can just amend it to int flush it, amend it back to identity and repopulate

Thanks

Damian.

 

[SQLDenis]

To reset the identity use
TRUNCATE TableName
or

DBCC CHECKIDENT (TableName, RESEED, 1)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[collierd]

Thanks, useful to know SQLDenis

I have the following code (as per BOL):

begin transaction
   alter table PAP03_TEST.dbo.holdslines
      alter column pk_orderline int IDENTITY(1,1)
commit

I have tried slotting int into a number of different places
e.g.
alter column pk_orderline IDENTITY(int,1,1)

It comes back with incorrect syntax near IDENTITY

Any thoughts?

Damian.

 

[SQLDenis]

Nope won't work like that
This is how SQL server does it behind the scenes

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_holdslines
	(
	ID int NOT NULL IDENTITY (1, 1)
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_holdslines ON
GO
IF EXISTS(SELECT * FROM dbo.holdslines)
	 EXEC('INSERT INTO dbo.Tmp_holdslines (ID)
		SELECT ID FROM dbo.holdslines TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_holdslines OFF
GO
DROP TABLE dbo.holdslines
GO
EXECUTE sp_rename N'dbo.Tmp_holdslines', N'holdslines', 'OBJECT'
GO
COMMIT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[collierd]

So you can't amend a field to an identity type
I guess the easiest thing to do is to drop and recreate the table

Can I amend an identity to an int or will it treat that field as an int, therefore alter is not necessary?

I could then simply create the table with an identity field leave it and use your method of truncation to clear it

Think I was over complicating matters as I am using some tables previously created by an external supplier who used this field in a number of locations, created it using identity and altered it to int (I was trying to do the reverse)

Thanks

Damian.

 

[SQLDenis]

you can add an identity field but you can't make an int field an identity

this will work

create table testid (name varchar(50))

alter table testid
add  id int identity

insert into testid values('test')

select * from testid

drop table testid

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[collierd]

thanks for the advice