inserting max(count)+1 in RowID field

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

Transact-SQL (2008)

inserting max(count)+1 in RowID field

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-26 : 16:05:21

Hi
I have two tables: one is actual customers table and other is staging table

The customers table has a RowID field and when i import data from Customer_Staging i would like to check the max(rowid) and start from the next number(max(rowID)+1)so on ..if the table is null it should start at 1 and then so on......

How can i achieve this when i am using simple insert statement..

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-27 : 02:47:01

see here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175403

A little change in case of there are no rows in destination:
select @offset=isnull(max(id),0) from DestTable

Too old to Rock'n'Roll too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-27 : 06:33:03

Even better, change your ID to an identity field, and you will never have to mess with it!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-27 : 10:47:11

Thank You Webfred for you repsonse..but i am having a strange problem..May i was not clear in my question ..but now my sql statement looks like this

MERGE Customers AS C
USING NewCustomers AS NC
ON C.LastName = NC. LastName
WHEN MATCHED THEN
UPDATE SET C.FirstName = NC.FirstName
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName) VALUES (NC.LastName,NC.FirstName);

My customers table has RowID..how can i insert the Rowid here for each row when updating data from newcustomers

Thank you..This what i tried but does not work

Says 'Windowed functions can only appear in the SELECT or ORDER BY clauses.'

DECLARE @offset INT
select @offset=isnull(max(RowID),0) from Customers
MERGE Customers AS C
USING NewCustomers AS NC
ON C.LastName = NC. LastName
WHEN MATCHED THEN
UPDATE SET C.FirstName = NC.FirstName
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName,RowID) VALUES (NC.LastName,NC.FirstName,row_number() over(order by id)+@offset);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 10:57:14

as suggested before, why cant you've RowID as identity in which case it will get values by itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-09-27 : 11:26:44

The Row Id field nor the table is not owned by me ..I just have permission to insert it in the following way...

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 11:33:17

then try this


DECLARE @offset INT 
select @offset=isnull(max(RowID),0) from Customers
MERGE Customers AS C
USING (SELECT ROW_NUMBER() OVER ORDER BY ID) AS Seq,*
FROM NewCustomers) AS NC
ON C.LastName = NC. LastName
WHEN MATCHED THEN
UPDATE SET C.FirstName = NC.FirstName
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName,RowID) VALUES (NC.LastName,NC.FirstName,NC.Seq +@offset);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources