|
The fact you have a while-loop is filling me horror. However, without being able to see the SP code we can't possibly comment on what might be going wrong.
You have contradicted yourself however, you stated Quote: We insert an initial value wherein the identity key value is -1,
but you state Quote: Both server have the table's field name defined with identity_seed as 0, and increment as 1.
Without checking I can't confirm, but perhaps there was a hole in 2008 that allowed you to trample all over the IDENTITY column.
You should use IDENTITY (-1,1) if you want the first key to be -1 (but I suspect you don't).
Try posting the code so we can suggest ways of improving it ... like getting rid of the WHILE loop 
|
|
|
|
|
Sounds like you're RESEEDing the table:
If rows are present in the table, the next row is inserted with the new_reseed_value value.
In version SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value + the current increment value.
So if you reseed to 0, and your increment is 1, the next value in SQL 2008 R2 or earlier will be 1, whereas the next value in SQL 2012 or later will be 0.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
Thank you, that is helpful!
|
|
|
|
|
VK19 wrote: Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1?
The answer was above, but I am curious why it matters.
Certainly existing data would long be past zero or one. And if this is new data, always, then why does it matter?
|
|
|
|
|
This is part of our datawarehouse project.
We have some reports set up for data extract, and using some <> 0 conditions.
|
|
|
|
|
So then adjust your index to start at 100 just to be sure.
|
|
|
|
|
Hi,
I am using a merge statement in my Stored Proc, when I am trying to get inserted values, its giving me all the matched and unmatched ones.
In the example below:
MERGE [dbo].[Service_Program_Detail] AS T
USING (select *, @PK_Target 'PK_Target' from [dbo].[Service_Program_Detail]
where FK_Program_Code_ServiceProgDetails = @PK_Source
and FK_Fiscal_Period_Code_ServiceProgDetails=@Fiscal_Period_Code) AS S
ON ((T.FK_Service_Code_ServiceProgDetails = S.FK_Service_Code_ServiceProgDetails)
and (T.FK_Fiscal_Period_Code_ServiceProgDetails=S.FK_Fiscal_Period_Code_ServiceProgDetails)
and (T.FK_Program_Code_ServiceProgDetails=S.PK_Target))
WHEN NOT MATCHED
THEN INSERT(FK_Service_Code_ServiceProgDetails, FK_Program_Code_ServiceProgDetails
, FK_Fiscal_Period_Code_ServiceProgDetails, CreatedBy, ModifiedBy
, CreatedOn, ModifiedOn, FK_Service_Program_Detail_Unit_Types)
VALUES(S.FK_Service_Code_ServiceProgDetails, S.PK_Target
, S.FK_Fiscal_Period_Code_ServiceProgDetails, @ExecutedBy, @ExecutedBy,
getdate(), getdate(), FK_Service_Program_Detail_Unit_Types)
WHEN MATCHED
THEN UPDATE SET T.FK_Service_Program_Detail_Unit_Types = S.FK_Service_Program_Detail_Unit_Types
OUTPUT inserted.PK_Service_Program_Detail,
inserted.FK_Service_Code_ServiceProgDetails,
@PK_Source,
inserted.FK_Program_Code_ServiceProgDetails,
inserted.FK_Fiscal_Period_Code_ServiceProgDetails
INTO @Service_Program_Detail
(
PK_Service_Program_Detail_T,
FK_Service_Code_ServiceProgDetails,
Program_Code_S,
Program_Code_T,
FK_Fiscal_Period_Code_ServiceProgDetails
);
the output statement is inserting not matched values and matched values, but I want only not matched values to be inserted into @Service_Program_Detail. My question is, is there any possibility that I can output matched and not matched values separately or I want only not matched values (which I could able to do by putting only not matched condition, but I want to update the value as well but it shouldn't be inserted into @Service_Program_Detail and it should be able to go into a different value or table variable.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
In the output clause you can specify the $action column that tells you whether it was an insert, update or a delete. And since you only have updates when matched...
|
|
|
|
|
Any help to figure out this query is highly appreciated.
I have three tables. (Scripts to load test data copied below).
First table #UserAccounts has UserAccounts, AccountType and BillingAccountKey.
Second table #BillingTransactions had Billingtransactionkey(surrogate key), BillingAccountKey and BillingCode.
UserAccount and BillingAccountKey has 1 to 1 relationship.
Third Table #BillingCodeRank has BillingCode and Rank.
I want to see the billing code associated with each UserAccount (with account type 'O') that has the max rank.
Example:UserAccount 456 has BillingAccountKey =2 and has three billing codes (222,333,444) having rank 6,5,4 recpectively.
The result should show BillingCode 222 associated with UserAccount 456 because codee 222 has the max rank among the three codes.
The final result from the test data would be as shown below:
Result:
UserAccount AccountType BillingAccountKey BillingCode Rank
456 O 2 222 6
789 O 3 111 7
102 O 5 333 5
--Scripts to load test data
SELECT * INTO #UserAccount FROM (
SELECT 123 AS UserAccounts, 'I' AS AccountType, 1 AS BillingAccountKey
UNION ALL
SELECT 456, 'O', 2
UNION ALL
SELECT 789, 'O', 3
UNION ALL
SELECT 101, 'I', 4
UNION ALL
SELECT 102, 'O', 5) A
SELECT * FROM #UserAccount
--================================================================
SELECT * INTO #BillingTransactions FROM (
SELECT 1 AS BillingTransactionKey, 1 AS BillingAccountKey, 111 AS BillingCode
UNION ALL
SELECT 2, 2, 222
UNION ALL
SELECT 3, 2, 333
UNION ALL
SELECT 4, 2, 444
UNION ALL
SELECT 5, 3, 111
UNION ALL
SELECT 6, 3, 555
UNION ALL
SELECT 7, 3, 666
UNION ALL
SELECT 8, 3, 222
UNION ALL
SELECT 9, 5, 333
UNION ALL
SELECT 10, 5, 777)A
SELECT * FROM #BillingTransactions
--===============================================
SELECT * INTO #BillingCodeRank FROM(
SELECT 111 AS BillingCode, 7 AS [Rank]
UNION ALL
SELECT 222, 6
UNION ALL
SELECT 333, 5
UNION ALL
SELECT 444, 4
UNION ALL
SELECT 555, 3
UNION ALL
SELECT 666, 2
UNION ALL
SELECT 777, 1
UNION ALL
SELECT 888, 1
UNION ALL
SELECT 999, 3
UNION ALL
SELECT 101, 5)A
SELECT * FROM #BillingCodeRank
Thank you for you help.
|
|
|
|
|
Something like this should work:
WITH cteOrderedBillingCodes As
(
SELECT
T.BillingAccountKey,
T.BillingCode,
R.Rank,
ROW_NUMBER() OVER (PARTITION BY T.BillingAccountKey ORDER BY R.Rank DESC) As RN
FROM
#BillingTransactions As T
INNER JOIN #BillingCodeRank As R
ON R.BillingCode = T.BillingCode
)
SELECT
A.UserAccount,
A.AccountType,
A.BillingAccountKey,
C.BillingCode,
C.Rank
FROM
#UserAccount As A
LEFT JOIN cteOrderedBillingCodes As C
ON C.BillingAccountKey = A.BillingAccountKey
And C.RN = 1
WHERE
A.AccountType = 'O'
;
ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
sorry its very complex to me.
|
|
|
|
|
I am stuck in the middle of a project. I am having an issue with adding the data source in "Entity Data Model Wizard"
Navigation Path:::::
Solution Explorer : Models(Folder): Add : New Item : ADO.NET Entity Data Model : EF Designer From Database : New Connection
Now here in "New Connection" I want to connect my postgresql database using Postgresql Datasource but I have only SQL Server datasource option there, Anybody know how to deal with this ERROR..??
Note: I have added Npgsql, entityframework5.Npgsql through NuGet.
|
|
|
|
|
|
Message Closed
modified 27-Feb-18 8:31am.
|
|
|
|
|
You've already had an answer posted.
And remember, the people who answer questions here are volunteers. Nobody is being paid to give you an answer in a fixed amount of time.
Try engaging with the people who are trying to help you, rather than flouting the rules of the site.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Message Closed
modified 27-Feb-18 8:31am.
|
|
|
|
|
So talk to the person who posted it!
And I say again: the people who answer questions here are volunteers. Have a little patience.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have posted it .. But I dont find any option to delete it..
|
|
|
|
|
Hello everybody,
i'm writing to ask an advice.
In my Access db, I've many tables but the most important table is named "Products".
The Primary Key for this table is the "Product Code".
For every product I've to save different fields. Some fields are descriptives fields that rarely change while other fields are prices fields that change different times per day.
Which is the best way to represent a product? I have these 3 ideas:
1) TABLE1 where I insert only descriptives fields and has the product code as primary key. TABLE2, where i save only prices, that has a relation one to one with the first table and share the primary key with it.
2) TABLE1 where I insert descriptives fields and an integer field at the end which is in relation with TABLE2 where I save prices. In this case the primary key of table2 will be an identity field. Also in this case it will be a one to one relation. I'm sceptic for this solution because I always add and remove products from table1 so, after 1 year it will be possible that I will have 10.000 product in my database but the primary key of TABLE2 will be a very big number like 50.000.
3) Only TABLE1 where I save every fields both desctiptives and prices fields.
Which is the best solution in your opinion? In this moment i've adopted the first solution...
Thank
Giacomo
|
|
|
|
|
It depends.
If a product can only ever have a single price, with no price-breaks, no customer-specific prices, etc., then store that price in the products table.
If you need a more complicated pricing structure, then store the prices in a separate table, with a one-to-many relationship between products and prices.
For example:
Prices:
ID (PK, Identity)
ProductCode (FK to products)
FromDate (optional)
ToDate (optional)
MinimumQuantity (optional)
MaximumQuantity (optional)
CustomerPriceGroup (optional)
Price
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much Richard....I think i will use your solution that is similar to my idea number 2 because, i forgot to explain, I will have to store different prices for every products....
|
|
|
|
|
If the ProduceCode is user editable then it should never be used as a foreign or primary key, use the ProductID an identity field.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When I'll insert a product I'll also insert the ProductCode but then I never won't be able to modify it. I'll only be able to remove the product
|
|
|
|
|
JackMisani wrote: Which is the best solution in your opinion?
A normalized model. Rules for normalization can be found on the wiki 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
