Increment

[Steve95]

Hi All

I have created a sp in sql 2005 and from my table variable I am inserting into a live table and the last column in the live table is a colum labelled id data type: int. The values in this are incrementing by 1, I need to know what to put in my insert statement that will populate the live table.

I hope I make sense.

If any questions please ask.

Many Thanks

 

[bborissov]

If that column is IDENTITY() column, don't put anything in your INSERT statement that could affect this colimn, something like:
(this is just an example)

[COLOR=blue]DECLARE[/color] @Test1 [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](200), Fld2 [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1))
[COLOR=blue]DECLARE[/color] @Test2 [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](200))

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test2 [COLOR=blue]VALUES[/color] ([COLOR=red]'aaaaaaa'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test2 [COLOR=blue]VALUES[/color] ([COLOR=red]'bbbbbbb'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test2 [COLOR=blue]VALUES[/color] ([COLOR=red]'ccccccc'[/color])

[COLOR=green]-- Let's insert all the recordsd from @Test2 into @Test1,
[/color][COLOR=green]-- See the last column in @Test1 is int IDENTITY() column
[/color]
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test1
[COLOR=blue]SELECT[/color] Fld1 [COLOR=blue]FROM[/color] @Test2

[COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] @Test1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Steve95]

Cheers for the above but the id column is not a ID column as one would expect. I have had a look at the numbers and they are not even incrementing at some stages, which I do not understand any ideas?

What would best approach to change this column to a genuine id column?

 

[bborissov]

Drop the column, Save table, Add New column with the same name and type and make it identity.

BE CAREFUL!
That could cause all FrontEnd applications to bomb, because they are planned to update that column.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP