pass variable to identity function

is it possible to pass a variable to an identity funtion

example

declare @.max_note int

select @.max_note = max(key ) from notes

select m_key = identity( int, @.max_note, 1),
name

into #prod_note

from prod_note

this one challeged me a lot. heres a solution

insert into #temp

delete from #temp

and do insert again

USE NORTHWIND
declare @.cmd nvarchar(4000)
declare @.max_note int
select @.max_note =max(employeeid ) from employees
SELECT IDENTITY(int, 1,1) AS IDNUM,LASTNAME INTO #TEMP FROM EMPLOYEES
--SELECT * FROM #TEMP
DELETE FROM #TEMP
INSERT #TEMP(LASTNAME)
SELECT LASTNAME FROM EMPLOYEES
SELECT * FROM #TEMP

DROP TABLE #TEMP

not the best solution but works the same

|||

Identity function doesn't support variables for the seed or increment. So you have several options since you are creating a temporary table:

1. Create the temporary table without the identity column and then do an ALTER TABLE to add the identity column using dynamic SQL like:

exec('alter table #prod_note add m_key int identity(' + @.max_note_val + ', 1)')

2. Create an empty table using CREATE TABLE and then use DBCC CHECKIDENT (this accepts variable for seed parameter) to reseed the table before doing the insert. This will be fine if you are dumping small number of rows into the temporary table & performance is not the main criteria. The main reason being that SELECT INTO is the fastest way to create a temporary table with data and doing CREATE followed by INSERT...SELECT will be slower.

|||This answer above is awesome!

pass a value

is it possible to do:
(A)
declare @.numberofitems Int
@.numberofitems = select max(itemorder)
from store, department, etc.

and pass the @.numberofitems to a #tempStore table, like:
(B)
(store, department, @.numberofitems,...)

I got itemorder but not the number of items in each department
Alex

--
Sent by 3 from yahoo part from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/newHi

You don;t give enough detail to produce an exact query. Please post DDL,
example data (as insert statements) and expected output if you require a
more precise answer.

At a guess something like:

SELECT S.Store, D.Department, max(I.itemorder) as NumberOfItems
from store S JOIN department D ON S.StoreId = D.StoreId
JOIN ItemsOrders I On I.DeptId = D.DeptId
GROUP BY S.Store, D.Department

or

SELECT S.Store, D.Department, ( SELECT max(I.itemorder) FROM ItemsOrders I
WHERE I.DeptId = D.DeptId AND S.StoreId = I.StoreId ) as NumberOfItems
FROM store S JOIN department D ON S.StoreId = D.StoreId

John

etc"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1062471242.1626678466@.host-66-81-78-52.rev.o1.com...
> is it possible to do:
> (A)
> declare @.numberofitems Int
> @.numberofitems = select max(itemorder)
> from store, department, etc.
> and pass the @.numberofitems to a #tempStore table, like:
> (B)
> (store, department, @.numberofitems,...)
> I got itemorder but not the number of items in each department
> Alex
>
>
> --
> Sent by 3 from yahoo part from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new|||u128845214@.spawnkill.ip-mobilphone.net (alexqa2003@.yahoo.com) wrote in message news:<l.1062471242.1626678466@.host-66-81-78-52.rev.o1.com>...
> is it possible to do:
> (A)
> declare @.numberofitems Int
> @.numberofitems = select max(itemorder)
> from store, department, etc.
> and pass the @.numberofitems to a #tempStore table, like:
> (B)
> (store, department, @.numberofitems,...)
> I got itemorder but not the number of items in each department
> Alex

It's not really clear from your post what you're trying to do, but it
may be something like this:

insert into #tempStore
(store, department, numberofitems)
select store, department, max(itemorder)
from orders
group by store, department

Or maybe this:

insert into #tempStore
(store, department, numberofitems)
select store, department, count(itemorder)
from orders
group by store, department

If this doesn't help, then it would be good if you can post your DDL
(CREATE TABLE statements), along with some sample data and the
expected output.

Simon