Incrementing ID in INSERT INTO ... SELECT statement

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 2000 Forums

SQL Server Development (2000)

Incrementing ID in INSERT INTO ... SELECT statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-17 : 09:16:43

Biju writes "I have two tables TableA and TableB. Is there anyway to insert an incremental value into the primaryKey column of TableA while inserting data of TableB into TableA using INSERT INTO. My Query looks like


INSERT INTO 
   TableA (P_KEY , TableA_COL1)
SELECT 
   max(TableA)+1 , TableB_COL1
FROM
   TableB




Note : max(TableA)+1 is pseudo code.I want the first col of 
select to increment from the max value of TableA P_KEY. I cant 
change my schema to have identity column nor can i use temp 
tables because the query is to be generated dynamically by code 
and the requirement is no database dependency.

SQL Server 2000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-17 : 09:22:40

Is it me, or does this sound like homework? I know employers are not supposed to be rational anyway, but come on:

quote:
because the query is to be generated dynamically by code and the requirement is no database dependency.

What moron thinks these things up?

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-17 : 09:25:47

quote:

What moron thinks these things up?

Still, it's not entirely beyond the bounds of possibility. Would certainly be a weird homework excercise, too. Unless the class is in bizarre database scenarios 101.

I'm not sure I can post an SQL answer to the question though whilst adhering to the rule of "no database dependency".

-------
Moo.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-17 : 09:28:39

No database dependency?
That's a bit tricky.
Suspect you could use a correlated subquery but not all databases support those.
Derived table - same again.

In fact don't think it is possible to write this so that it could work on any database (even if limitted to those that support sql).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-17 : 09:34:53

Use a natural key ... problem solved.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources