May 27, 2014 at 2:34 am
i have this update query
INSERT INTO dbo.BNYWorkingSecTable
( SECTION)
SELECT SECTION
FROM dbo.GLAccounts1
WHERE NOT EXISTS ( SELECT *
FROM dbo.BNYWorkingSecTable
WHERE dbo.BNYWorkingSecTable.SECTION = dbo.GLAccounts1.SECTION)
i want to use a select max and enter that value into the table. how can i get
SELECT MAX(sectionnumber) FROM dbo.BNYWorkingSecTable to work with above update
May 27, 2014 at 4:19 am
Can anyone help with this
May 27, 2014 at 6:49 am
Guessing at what you are trying to achieve here - but I'll take a stab:
DECLARE @MAXVALUE INT
SET @MAXVALUE =
(
SELECT MAX(sectionnumber)
FROM dbo.BNYWorkingSecTable
)
INSERT INTO dbo.BNYWorkingSecTable
(
SECTION
,SOMECOLUMN
)
SELECT
SECTION
,@MAXVALUE
FROM
dbo.GLAccounts1
WHERE NOT EXISTS
(
SELECT *
FROM dbo.BNYWorkingSecTable
WHERE dbo.BNYWorkingSecTable.SECTION = dbo.GLAccounts1.SECTION
)
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 27, 2014 at 7:04 am
good guess
thanks for help
May 27, 2014 at 7:05 am
No probs 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 27, 2014 at 7:12 am
Be careful, that's not necessarily going to work under concurrent calls if it's a manual 'identity' column (two queries could get the same max value)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2014 at 7:58 am
how could i over come this problem as more than likely there will be more thqan 1 been updated at a time
May 28, 2014 at 11:36 am
Depends. Is anything changing the value of MAX(sectionnumber) in dbo.BNYWorkingSecTable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 29, 2014 at 1:45 am
Any reason you cant use an IDENTITY?
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 29, 2014 at 5:08 am
ya well once the new number is created its then stored in a file and re read into table so its always there.
it works well for the one account but when i tried more like u said they all had the next number i do need the unique as its creating account codes for out system.
on the using identy
when i tried this the new stuff would become the number of old stuuf. im not explaing it will but this might help.
table before insert
SECTIOn Section number
a 1
b 2
c 3
when i ran the update and re fed it into the table it was like this
SECTIOn Section number
d 1
a 2
b 3
c 4
i need the numbers to stay the exact same with each letter
so a always has to be 1
May 29, 2014 at 5:50 am
ronan.healy (5/29/2014)
ya well once the new number is created its then stored in a file and re read into table so its always there.it works well for the one account but when i tried more like u said they all had the next number i do need the unique as its creating account codes for out system.
on the using identy
when i tried this the new stuff would become the number of old stuuf. im not explaing it will but this might help.
table before insert
SECTIOn Section number
a 1
b 2
c 3
when i ran the update and re fed it into the table it was like this
SECTIOn Section number
d 1
a 2
b 3
c 4
i need the numbers to stay the exact same with each letter
so a always has to be 1
Can't you add ROW_NUMBER() to MAX() when you're adding more than one at a time?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 29, 2014 at 9:16 am
where would i put it in the first slect
SET @MAXVALUE =
(
SELECT MAX(sectionnumber)+1,
FROM dbo.BNYWorkingSecTable
)
May 29, 2014 at 9:43 am
ronan.healy (5/29/2014)
where would i put it in the first slect
SET @MAXVALUE =
(
SELECT MAX(sectionnumber)+1,
FROM dbo.BNYWorkingSecTable
)
How about a little bargaining here - you spend a little time and effort describing your problem, and folks here spend a little time on fixing it? Maybe it's just me, maybe I'm getting old, but I actually can't make head or tail of what you are trying to do.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 29, 2014 at 9:56 am
i want to add data to a table and have the number in the sectionnumber part to increment by one every time it finds new data.
the code i used for this is
DECLARE @MAXVALUE INT
SET @MAXVALUE =
(
SELECT MAX(sectionnumber)
FROM dbo.BNYWorkingSecTable
)
INSERT INTO dbo.BNYWorkingSecTable
(
SECTION
,SOMECOLUMN
)
SELECT
SECTION
,@MAXVALUE
FROM
dbo.GLAccounts1
WHERE NOT EXISTS
(
SELECT *
FROM dbo.BNYWorkingSecTable
WHERE dbo.BNYWorkingSecTable.SECTION = dbo.GLAccounts1.SECTION
)
which works fine if only 1 number it been added at a time. but the likely hood is that at some point there could be more than 1 been added and this doesnt work as sectionnumber ends up getting the same number.
im trying to get the section number to be able to incremenet by 1 everytime no matter if 1 number is been added or 100 numbers at the same time
May 29, 2014 at 10:07 am
What you keep describing is an identity but there is some issue with identity in your case that I don't fully understand. I can say from experience that trying to roll your own is fraught with countless issues. You have to deal with concurrency and a host of other things that are damn difficult to get right.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply