SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 inserting max(count)+1 in RowID field		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
283 Posts
Posted - 09/26/2012 :  16:05:21  Show Profile  Reply with Quote
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
Edited by - akpaga on 09/26/2012 21:23:40

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 09/27/2012 :  02:47:01  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 09/27/2012 :  06:33:03  Show Profile  Reply with Quote
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
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
283 Posts
Posted - 09/27/2012 :  10:47:11  Show Profile  Reply with Quote
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);
Edited by - akpaga on 09/27/2012 10:56:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 09/27/2012 :  10:57:14  Show Profile  Reply with Quote
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/
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
283 Posts
Posted - 09/27/2012 :  11:26:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 09/27/2012 :  11:33:17  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000