sql insert problem

[kieran]

INSERT INTO Comments_tbl (FullName_tsf, Email_tsf, NTuser_tsf,
name_tsf, type_tsf, comments_tsf) VALUES ('Test Tester' ,
'tester@test.co m', 'ttest', 'test', 'help', 'need better help')

I am trying to use the following sql to insert from a script but there
is a primary key column - 'com_no' - on the table.

How do I write the sql insert so that the 'com_no' value gets a value
inserted into and increases each time.

Thanks for all help.

[Simon Hayes]

Re: sql insert problem


"kieran" <kieran5405@hot mail.com> wrote in message
news:b39796b3.0 406141026.5d6e7 da0@posting.goo gle.com...[color=blue]
> INSERT INTO Comments_tbl (FullName_tsf, Email_tsf, NTuser_tsf,
> name_tsf, type_tsf, comments_tsf) VALUES ('Test Tester' ,
> 'tester@test.co m', 'ttest', 'test', 'help', 'need better help')
>
> I am trying to use the following sql to insert from a script but there
> is a primary key column - 'com_no' - on the table.
>
> How do I write the sql insert so that the 'com_no' value gets a value
> inserted into and increases each time.
>
> Thanks for all help.[/color]

I'm not completely sure from your description, but I guess you're looking
for an IDENTITY column, which will automatically generate a new value for
each INSERT? Unfortunately, you can't add the IDENTITY property to an
existing table, so you would need to create the table again:

create table dbo.Comments (
CommentID int identity(1,1) not null,
FullName ...,
...,
constraint PK_Comments primary key nonclustered (CommentID)
)

After the INSERT, you can use the function SCOPE_IDENTITY( ) to get the value
that was just inserted.

Simon

[kieran]

Re: sql insert problem

Thanks Simon.

That clears up everything for me.





"Simon Hayes" <sql@hayes.ch > wrote in message news:<40cdf3be$ 1_1@news.bluewi n.ch>...[color=blue]
> "kieran" <kieran5405@hot mail.com> wrote in message
> news:b39796b3.0 406141026.5d6e7 da0@posting.goo gle.com...[color=green]
> > INSERT INTO Comments_tbl (FullName_tsf, Email_tsf, NTuser_tsf,
> > name_tsf, type_tsf, comments_tsf) VALUES ('Test Tester' ,
> > 'tester@test.co m', 'ttest', 'test', 'help', 'need better help')
> >
> > I am trying to use the following sql to insert from a script but there
> > is a primary key column - 'com_no' - on the table.
> >
> > How do I write the sql insert so that the 'com_no' value gets a value
> > inserted into and increases each time.
> >
> > Thanks for all help.[/color]
>
> I'm not completely sure from your description, but I guess you're looking
> for an IDENTITY column, which will automatically generate a new value for
> each INSERT? Unfortunately, you can't add the IDENTITY property to an
> existing table, so you would need to create the table again:
>
> create table dbo.Comments (
> CommentID int identity(1,1) not null,
> FullName ...,
> ...,
> constraint PK_Comments primary key nonclustered (CommentID)
> )
>
> After the INSERT, you can use the function SCOPE_IDENTITY( ) to get the value
> that was just inserted.
>
> Simon[/color]

[sa]

Re: sql insert problem

Hello Simon,

Suppose, unlike Kieran's case, I have a table with one column set as
primary key, which is not supposed to be duplicated. This key has a
corresponding textbox on the form which the user enters manually.

I use .ExecuteNonQuer y() after setting up the parameters of a
SQLCommand object to insert a row. The SP does a simple insert
routine.

Using one single button for updating the table (new row/existing row),
is there a simple / automatic method to modify an existing row in the
table (using Triggers, etc.) ?

Or, do I have to write the code in the button that will -

first, fire an sp that returns back a boolean indicating whether a row
with the user-entered primary key already exists, then

second, conditionally set the .commandtext value to either of the two
SPs (INSERTSP / UPDATESP) depending on whether the boolean was
row-exists/does-not-exist.

finally, fire the .executenonquer y() ?

Any help will be highly appreciated.

Regards,

Sanjay.

[Zach Wells]

Re: sql insert problem

sa wrote:[color=blue]
> Hello Simon,
>
> Suppose, unlike Kieran's case, I have a table with one column set as
> primary key, which is not supposed to be duplicated. This key has a
> corresponding textbox on the form which the user enters manually.[/color]

Then you'd have a bad design. :) You really shouldn't allow primary keys
to be changed, especially by the user. If you find yourself changing
your primary keys so often that you need a data entry field, then
rethink your primary key. That said...

What you can do is test for the row's existance in the stored procedure.
Basically, your stored procedure can do one or the other depending on
the case, no need for multiple procedures:

IF EXISTS(SELECT statement to check for the row)
UPDATE Statement
ELSE
INSERT statement

Zach

[Carl H]

Re: sql insert problem

try this...

declare @com_no int,
@cnt int,
@rowcnt int


select @com_no =max(com_no) from Comments_tbl
select @cnt=0,
@rowcnt=count(* ) from <table with rows to insert, or hard code a
number>

while @cnt < @rowcnt
begin
INSERT INTO Comments_tbl (com_no, FullName_tsf, Email_tsf,
NTuser_tsf,
name_tsf, type_tsf, comments_tsf) VALUES (@com_no, 'Test Tester' ,
'tester@test.co m', 'ttest', 'test', 'help', 'need better help')
select @com_no-@comno+1

if @cnt>@rowcnt break else continue
end


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!