November 5, 2012 at 9:19 am
Hello all,
Quick question
I got a table with a varchar(20) field as primary key
the result has to be
W0001
W0002
....
The data is coming from existing tables and I'm using a merge statement to see wheiter the record is already present or not
Anyone got an idea how to create the autoincrement
Cheers
Resender
November 5, 2012 at 9:38 am
Resender (11/5/2012)
Hello all,Quick question
I got a table with a varchar(20) field as primary key
the result has to be
W0001
W0002
....
The data is coming from existing tables and I'm using a merge statement to see wheiter the record is already present or not
Anyone got an idea how to create the autoincrement
Cheers
Resender
This question comes up around here at least once a week. It is not a good design at all. You have to make your key a varchar to do this and you have effectively limited the table to a max of 9,999 rows. What happens after W9999? does it become W10000? You can't order them correctly now because varchars sort differently than numbers.
;with cte as
(
select 'W1000' as KeyField union all
select 'W9999' union all
select 'W10000'
)
select KeyField
from cte
order by KeyField
What are you hoping to gain from this?
_______________________________________________________________
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/
November 6, 2012 at 12:41 am
//Brilliant I made the post with my text not appearing so here goes the edit
The scenario is a bit more complicated then I initially described, we have to 'merge' data from several databases to a single one.
All the databases are SQL Server databases and all of them are the source databases of ERP programs (so bye bye logic on that part), the destination database is the source for a Microsoft Dynamics Nav product. (So in my opinion a very poorly designed database but then the native database Dynamics used to run on was an OO-database type and Microsoft translated it to a relational one)
And I can't change the fields cause it's a root I don't have a license for to make any changes on(field changes & also any change I do can be undone by a product update in the future) , so I need a way to respect the Nav number series in the table while inserting the new data
When I mean 'merge' I'm not talking about the merge function but about the literal merging of the data of 2 or more sources, since the client I'm doing it for used a separate ERP program for sales then they did for invoices and inventories; and now they want to use a single one for the entire operation.:-P
November 6, 2012 at 2:54 am
I agree with Sean on the dangers and issues with this. But if you must you must.
To get around the sorting issue he notes, there is always this:
;with cte as
(
select 'W1000' as KeyField union all
select 'W9999' union all
select 'W10000'
)
select KeyField
from cte
order by LEFT(KeyField, 1), CAST(RIGHT(KeyField, LEN(KeyField)-1) AS BIGINT)
And a somewhat awkward but potentially effective approach to generating new keys would be something like this:
;WITH cte as (
select 'W1000' as KeyField union all
select 'W9999' union all
select 'W10000'),
cte2 AS (
SELECT MaxN=MAX(CAST(RIGHT(KeyField, LEN(KeyField)-1) AS BIGINT))
FROM cte),
MyNewData (n) AS (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3)
--INSERT INTO Table (KeyField)
SELECT 'W' + CAST(
n + (SELECT MaxN FROM cte2) -- Use ROW_NUMBER() instead of n if needed
AS VARCHAR(19))
FROM MyNewData
If this is a high transaction process inserting many new records constantly into the table, that may not work very well though. For that, you may need to SELECT with an UPDLOCK hint (the current max keyfield) within a transaction and then complete the transaction with your INSERT/MERGE.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2012 at 3:18 am
I actually agree with both of you but it's out of my hand, I'm limited by what the architecture allows me.
And I can't change the architecture, if I could I would
For instance the tables in the database are called in the following structure
databasename.dbo.companyname$tablename
I hope that that at least in the future it will be
databasename.companyname.tablename
As for the problem it's been resolved, I've been given permission to make a 'staging' table, the logic for the number series will then be done later in Nav itself when moving data to the main table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply