Insert statement error - SQL Server Forums

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)

Insert statement error

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 01:03:46

Hi all,
I have a table called test with two colums slno(int) and id(int).Now I want to insert the values into table by using this
qry.But it is giving error.

insert  test (slno,id) values (1,select isnull(max(id),0)+1 from test)

Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 01:13:39

[code]INSERT test (slno, [id])
SELECT 1, ISNULL(MAX([id]), 0) + 1
FROM test[/code]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 01:19:04

Thanks Khan,
How can I get back the values of the inserted id

Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 01:24:29

[code]SELECT MAX([id])
FROM test[/code]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 01:28:45

:).After Inserting I want to retrive the value using the

quote:

INSERT test (slno, [id])
SELECT 1, ISNULL(MAX([id]), 0) + 1
FROM test

Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 01:30:02

Wny not using an identity column ?

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 02:06:32

I want to retrive the just inserted id from the table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 02:09:31

You can use @@identity or scope_identity() to do that

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 02:13:54

But From MSDN I found this one

quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

But for this condtion the id is not a identity column ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 02:16:19

quote:
Originally posted by danasegarane76

But From MSDN I found this one
quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

But for this condtion the id is not a identity column ?

What i mean is why not using an identity column for the [ID] column ? then you can just make use of these functions, unless you have other requirements

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 02:18:04

Dear Khan,
Thanks for spending your valuable time.
I have told by my boss that dont use identity column for some reasons.For this I am not using that one.How can i do that without Scope_Identity

Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 02:27:37

you can't. Scope_identity is only for identity column.

Maybe this will be of your interest http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82566

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 02:49:52

How can I get the value using Scope_identity with that insert statement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 02:51:11

quote:
Originally posted by danasegarane76

How can I get the value using Scope_identity with that insert statement

You can't. Scope_identity only works with table with identity column

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 02:55:02

No problem.I can set the slno to identity column.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 02:56:54

quote:
I have told by my boss that dont use identity column for some reasons

Hope that won't get you into trouble

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 03:01:04

No probs. How to retrive the value

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 03:03:43

run this and see the result

CREATE TABLE #temp
(
	col	int,
	id_col	int identity(1,1)
)

INSERT INTO #temp (col) SELECT 10

SELECT	scope_identity()

INSERT INTO #temp (col) SELECT 20
INSERT INTO #temp (col) SELECT 30

SELECT	scope_identity()

SELECT * FROM #temp

DROP TABLE #temp

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 03:05:53

I think I have to write a SP to retrive the value

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-21 : 03:21:02

Now I am on the real job.I am creating a SP as follows


Create Procedure sp_GetIDForRecovery
--Created BY Danasekarane.A,21-07-2007
(
@User_ID nvarchar(6),
@Filename varchar(100),
@FileModifiedon datetime,
@Filesize bigint,
@System nvarchar(50),
@BookID bigint,
@StageID varchar(50),
@DivisionID varchar(50),
@WMSID smallint,
@Activityid int,
@JobCode varchar(100),
@RecoveryID bigint output
)
as
insert wms_TrnRecoveryDetails ([User_ID],[FileName],FileModifiedDate,Filesize,system,Bookid,stageid,Divisionid,wmsid,Activityid,jobcode)
values (@User_ID,@Filename,@FileModifiedon,@Filesize,@System,@BookID,@StageID,@DivisionID,
@WMSID,@Activityid,@JobCode)
Select @RecoveryID=scope_identity()
return @RecoveryID

and executing the same from QA as


Exec sp_GetIDForRecovery  '48','222',getdate,122,'123',12,'13','34',2,34,'43'

and I am getting the error as


Error converting data type nvarchar to datetime.

What could be reason

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 03:24:32

you can't pass a function directly into the stored procedure that way.

DECLARE @now datetime

SELECT @now = GETDATE()

EXEC sp_GetIDForRecovery  'is1348','222', @now ,122,'123',12,'13','34',2,34,'43'

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources