May 14, 2007 at 5:09 am
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
May 14, 2007 at 6:31 am
So you have a "live" table (one that's permanent in the database I presume) with a definition similar to
create table myTable( col1 int, col2 varchar(10), col3 float, col4 bit, ID int identity(1,1) )
When you insert data into the table, you can simply do
insert into myTable( col1, col2, col3, col4 ) values( 1, '2', 3, 0)
Since the identity field will be populate itself, you do not include it in the list of columns and needn't specify a value for it.
To obtain the value of ID just inserted you can look at the value returned by the SCOPE_IDENTITY() function.
May 14, 2007 at 6:48 am
cheers for the above, but this is it the live table is a int and column name id but Iam using sql 2005 and in the "Identity Specification" section "No" is selected and when I browsing through the ID column the numbering is not sequential. Any ideas how I can make this a genuine ID column.
When I leave in the insert statement the last value blank reflecting the ID column in the live table I get the following message :-
"Msg 213, Level 16, State 4, Line 46
Insert Error: Column name or number of supplied values does not match table definition"
May 14, 2007 at 7:56 am
You can make it autoincrement by setting the identity column.
If you don't want to do that, you have to calculate the next ID value and insert it into the table.
May 17, 2007 at 11:55 pm
calculating the next ID is not recommended. use identity.
---------------------------------------
elsasoft.org
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply