|
go to the Subject , i explain the problem and code also.
|
|
|
|
|
I have got a table say something like this:
ID VARCHAR(30),
ParentID VARCHAR(30),
TITLE NVARCHAR(100)
As it seems, this table has a parent-child relation with itself. the ID column is not auto-generated and is a combination of ASCII characters and numbers with length of 30.
Now, I want to move all the data contained in the above table to the following table:
ID SMALLINT
ParentID INT
TITLE NVARCHAR(100)
In this table, ID is an auto-generated integer Identity column starting from 1.
What is the best way for converting the ID columns while moving them to the new table?
Thanks in advance for any help 
|
|
|
|
|
I think you have to do this in two parts. If you have a natural key on the table, it's easiest to use that. Something like (just a draft, may contain several mistakes):
INSERT INTO TargetTable (Title)
SELECT Title
FROM SourceTable
--
UPDATE TargetTable
SET ParentID = (SELECT tt1.Id
FROM TargetTable tt1, SourceTable st1, SourceTable st2
WHERE targettable.Title = st1.Title
AND st2.Id = st1.ParentId
AND st2.Title = tt1.Title)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thank you, seems a very good idea. I will try it
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Shouldn't ID and ParentID have the same type? Are the Titles unique?
|
|
|
|
|
ID and ParentID are of the same type (both varchar in source table and INT in destination table)
Titles may not necessarily be unique. Therefor, I might have a title like 'business' whose parentId is NULL and have another title like 'business' whose parentId is 'POVAD78Y6CSDD8F76NEL876D'.
The grand parent of all items is an item whose parentId is NULL (a root element).
Could you suggest a good solution please?
|
|
|
|
|
Maysam Mahfouzi wrote: INT in destination table
That's not what this says:
ID SMALLINT
ParentID INT
Maysam Mahfouzi wrote: Titles may not necessarily be unique
That would seem to eliminate the other suggestion.
Maysam Mahfouzi wrote: Could you suggest a good solution please?
Maybe...
Perhaps you could add the existing varchar columns to the new table temporarily, copy the data over, then set the ParentID values based on the old mappings within the new table, and then remove the varchar IDs.
|
|
|
|
|
PIEBALDconsult wrote: That's not what this says:
ID SMALLINT
ParentID INT
I'm sorry think of those both as SMALLINT.
PIEBALDconsult wrote: That would seem to eliminate the other suggestion.
You are right, at that time I didn't know titles are not unique. I just found some duplicate titles.
I can't rely on old VARCHAR values to calculate new ID and ParentIDs in SMALLINT. How is it possible to move a string like 'LSFDKJSDLFKSJDLJSLDK' to a SMALLINT column?
|
|
|
|
|
Maysam Mahfouzi wrote: I can't rely on old VARCHAR values to calculate new ID
No, you're using auto-increment for the new IDs, right?
ID,ParentId,OldID,OldParentId,Description (or whatever it is)
INSERT INTO NewTable (OldID,OldParentId,Description) SELECT * FROM OldTable (add an ORDER BY clause if desired)
And you get all new IDs. You also have the original mappings. For any row with non-null OldParentId, match it to the row with that OldID to get the (new) ID to put in the (new) ParentID.
Something like this:
UPDATE NewTable
SET ParentId=B.ID
FROM NewTable A
INNER JOIN NewTable B
ON A.OldParentID=B.OldID
(I think I'll go test that now.)
Then remove the OldID and OldParentID columns (if desired).
|
|
|
|
|
So I think I've got to add OldID and OldParentID columns to the destination table, move data the way you've suggested and then remove OldID and OldParentID columns. This solution seems quite perfect and beautiful and I'm going to test it as soon as I have access to database. I'm also thinking of a way to move data without using those two temporary columns in target table.
I really really appreciate your help and time PIEBALDconsult
_
|
|
|
|
|
Just got around to testing it:
INSERT INTO NewTable (OldID,OldParentID,Description) SELECT * FROM OldTable
SELECT * FROM NewTable
UPDATE A
SET ParentID=B.ID
FROM NewTable A
INNER JOIN NewTable B
ON A.OldParentID=B.OldID
SELECT * FROM NewTable
It worked on the small amount of data I created to test it:
1 NULL 1 NULL Section 1
2 NULL 2 NULL Section 2
3 1 1.a 1 Section 1.a
4 1 1.b 1 Section 1.b
5 2 2.a 2 Section 2.a
6 2 2.b 2 Section 2.b
7 3 1.a.i 1.a Section 1.a.i
8 3 1.a.ii 1.a Section 1.a.ii
|
|
|
|
|
Thank you again, I tested it and it worked for me too
|
|
|
|
|
hai all
i am using sql server 2000.
when i worked in .net,suddenly sql server service manager is stopped.then i tried to start the sql server service manager in service...it showing error as "The MS SQLSERVER service on local computer started and then stopped.some services stop automatically if they have no work to do,for example,the performance logs and alerts service."how to solve this?
thanks in advance.
|
|
|
|
|
Anything in Event log (system or application).
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i can't understand...what u say....
|
|
|
|
|
If the service stops unexpectedly, open Event Viewer from control panel and check both Application log and System log. Find out if there are any error messages related to Service Manager.
Or do you mean the windows application which is found in Task bar and can be started from Start menu?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Is there an easy way to restrict values - returned by a SQL select - to certain boundaries?
I've got a some tables like this
dataTBL:
dt id x
2008-01-01 10:00 1 5.13
2008-01-01 10:00 2 14.00
2008-01-01 10:00 3 -2.10
2008-01-01 10:00 4 1.65
2008-01-01 11:00 1 5.19
...
compTBL (balancing computation rules):
resultID baseID k d minX maxX
100 1 +1.0 0 null null
100 2 -0.5 0 +3 null
100 3 -0.5 +5 -10 0
200 1 +1.0 0 null null
200 4 -1.0 10 0 100
...
And I want to calculate for ID 100 e.g. the values
5.13 * 1.0 + 0.0 (no limits)
+ 14.00 *-0.5 + 0.0 (but at least 3)
+ -2.10 *-0.5 + 5.0 (but at least -10 and maximal 0)
I'm working with Sql Server 2005 and at the moment I'm doing it this way
select
dt,
resultID,
sum(
case
when isnull(x,0) * k + d < minX then minX
when isnull(x,0) * k + d > maxX then maxX
else isnull(x,0) * k + d
end
)
from dataTBL join compTBL on dataID = baseID
group by dt, resultID
This works fine, but I don't like having the same expression three times just for testing <, > and else.
Is there no shorter way to write this case-expression? Or ist there some other function I could use? Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?
Thanks
Andy
|
|
|
|
|
ScruffR wrote: Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?
How about creating an scalar-valued function ClipToBoundaries?
Regards,
Syed Mehroz Alam
|
|
|
|
|
Of course!  I'm always thinking complicated 
I thought of a C# function, but didn't like this idea, because our admins would have to activate .Net support on their precious server 
SV-functions are a possible option, but how about performance? Is there a standard answer to that question SVF vs. CASE?
Thanks
Andy
|
|
|
|
|
ScruffR wrote: but how about performance?
Should be about the same, but it reduces maintenance and improves readability, both of which are worth a performance hit.
I use a lot of user defined functions, most of them I have in a database that contains only a library of them (and no data), that way I don't have to copy them to each database I use.
|
|
|
|
|
If you don't have the function you need in T-SQL (as in this case), create your own using either T-SQL or if you need heavier calculation yu can also build the function on CLR using C# and register it to SQL Server.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I have an Query that contains a column name a_b and also a column b_c.
column b_c have 2 values for every value of a_b means values of a_b is 2 then for this 2 b_c has two values i.e 7 and 9.
Can somebody help me out....
|
|
|
|
|
anoopazgar wrote: Can somebody help me out....
Probably, if we understood your problem. What is/isn't happening, what sql have you got so far?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: if we understood your problem
Yup. A big IF. Couldn't understand it at all.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
