Sybase INSERT INTO with Identity field?

Question

I have not seen this exact issue posted anywhere, and thus I'm starting to think it's just not possible, that I'm going about this the wrong way.

I'm trying to create a temp table with an auto-incrementing unique key using IDENTITY then using an INSERT INTO (fields...) SELECT @@identity, ... type statement, but I can't get it to work.

CREATE TABLE #someTemp (
    id INT IDENTITY UNIQUE,
    foo1 VARCHAR (20),
    foo2 VARCHAR (80),
    rand NUMERIC
)

INSERT INTO (id, foo1, foo2, rand)
SELECT id = @@identity, v.foo_bar, v.bar_foo, v.list
FROM #otherTemp a
INNER JOIN some_table v
    ON v.id = a.id
    AND v.suu = a.ooo
WHERE a.x = 'gotcha'

My code looks something more or less like this, however it gripes about illegal identity values, I have also tried using just @@identity without the id =, and a few other iterations.

Now I know that I can do something like this:

SELECT @@identity, blah, foo, bar
INTO #aTemp
FROM ...

INSERT INTO #someTemp (id, foo1, foo2, rand)
SELECT * FROM #aTemp

And this will work, but in my case I have anywhere from 1 to 5 iterations of this query executing with similar selects/froms and they all need to be joined together with a unique key for later calculations, and I don't know how many times it will be executed, so I would need to select each one into their own temp table, then insert them back into #someTemp later, and I would like to not go this route if there is a way to make this work.

If this already has an answer please link, or provide as much detail and examples as possible.

Thank you!

Answer 1

Why wouldn't you just insert like this?

INSERT INTO (foo1, foo2, rand)
SELECT v.foo_bar, v.bar_foo, v.list
FROM #otherTemp a INNER JOIN
     some_table v
     ON v.id = a.id AND v.suu = a.ooo
WHERE a.x = 'gotcha';

Leave the id out. It will be assigned automatically.