December 23, 2013 at 8:07 pm
Posted - 12/23/2013 : 18:25:50 Show Profile Reply with Quote
Here is my Test Data, Please any advise would be great appreciated.
CREATE TABLE Table1
(
ID NVARCHAR(50),
FName NVARCHAR(50),
LName NVARCHAR(50),
DOB NVARCHAR(50),
Active bit
)
Create Table Table2
(
ID NVARCHAR(50),
PID NVARCHAR(50),
Phone NVARCHAR(50),
Address NVARCHAR(50)
)
Create TABLE Tbale3
(
PID NVARCHAR(50),
OInfo NVARCHAR(50)
)
--Insert Table1
Insert into Table1 (ID,FNAME,LName,DOB)
VALUES ('1','Smith','Tom','12/12/2011')
Insert into Table1 (ID,FNAME,LName,DOB)
VALUES ('2','Smith','Tom','12/12/2011')
--Insert Table2
Insert into Table2 (ID,PID,PHONE,Address)
VALUES ('1','101','5178896566','Test Ave')
-- Insert Table3
Insert Into Tbale3
VALUES ('101','Active Order')
-- Here is my question....
I am Creating a Front End for this, the user is going to SELECT Witch rows they want to MERGE.
here is the update i need from first table
ID FName LName DOB ISactive
1 Smith Tom 12/12/2011 NULL
2 Smith Tom 12/12/2011 NULL
Let Say user select ID = 1 they wants to merge so I want to update in TABLE1 ISactive = 1 for ID=1
and in TABLE2, update ID = 2, data look like this
ID PID Phone Address
2 101 5178896566 Test Ave
and in TABLE3, UPDATE PID = 101
PIDOderInfo
101Active Order
Please feel free to let me know if my questino is not clear and doesn't make sens. Thanks in advance.
December 23, 2013 at 8:35 pm
Your field sizes and some of the types are a bit over the top.
DOB NVARCHAR(50)? Makes doing date math really hard.
ID NVARCHAR(50)? Can't you use an INT IDENTITY?
same for Phone
December 23, 2013 at 8:45 pm
Thats just a sample data, I just created.... Yes we can use DOB datetime, but ID not we can't use IDENTITY...
Please if u think, if u have some kinda solution, Post here...
Thank You
December 24, 2013 at 9:03 am
Any Advise guys?
Thank You.
December 24, 2013 at 5:50 pm
rocky_498 (12/23/2013)
--Insert Table1Insert into Table1 (ID,FNAME,LName,DOB)
VALUES ('1','Smith','Tom','12/12/2011')
Insert into Table1 (ID,FNAME,LName,DOB)
VALUES ('2','Smith','Tom','12/12/2011')
--Insert Table2
Insert into Table2 (ID,PID,PHONE,Address)
VALUES ('1','101','5178896566','Test Ave')
-- Insert Table3
Insert Into Tbale3
VALUES ('101','Active Order')
-- Here is my question....
I am Creating a Front End for this, the user is going to SELECT Witch rows they want to MERGE.
here is the update i need from first table
ID FName LName DOB ISactive
1 Smith Tom 12/12/2011 NULL
2 Smith Tom 12/12/2011 NULL
Let Say user select ID = 1 they wants to merge so I want to update in TABLE1 ISactive = 1 for ID=1
and in TABLE2, update ID = 2, data look like this
Since I don't see an ID=2 in Table2, does this mean you want to INSERT ID=2 into Table2? What is it about the update to Table1 that makes you want to INSERT a new row into Table2?
rocky_498 (12/23/2013)
ID PID Phone Address2 101 5178896566 Test Ave
and in TABLE3, UPDATE PID = 101
PIDOderInfo
101Active Order
Please feel free to let me know if my questino is not clear and doesn't make sens. Thanks in advance.
And I don't see anything changing in Table3...
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
December 24, 2013 at 8:00 pm
Here is the Little Correction... Please guide
CREATE TABLE Table1
(
ID NVARCHAR(50),
FName NVARCHAR(50),
LName NVARCHAR(50),
DOB NVARCHAR(50),
Active bit
)
Create Table Table2
(
ID NVARCHAR(50),
PID NVARCHAR(50),
Phone NVARCHAR(50),
Address NVARCHAR(50)
)
Create TABLE Table3
(
PID NVARCHAR(50),
OInfo NVARCHAR(50)
)
--Insert Table1
Insert into Table1 (ID,FNAME,LName,DOB)
VALUES ('1','Smith','Tom','12/12/2011')
Insert into Table1 (ID,FNAME,LName,DOB)
VALUES ('2','Smith','Tom','12/12/2011')
--Insert Table2
Insert into Table2 (ID,PID,PHONE,Address)
VALUES ('2','101','5178896566','Test Ave')
-- Insert Table3
Insert Into Table3
VALUES ('102','Active Order')
Select * from Table1
select * from Table2
select * from Table3
Here is the End result in Table1, Table2, Table3
Table1:-
IDFNameLNameDOBActive
1SmithTom12/12/2011NULL
2SmithTom12/12/20111
Table2:-
IDPIDPhoneAddress
11015178896566Test Ave
Table3:-
PIDOInfo
101Active Order
Here is the answers of your questions that you asked...
Since I don't see an ID=2 in Table2, does this mean you want to INSERT ID=2 into Table2?
Ans:- Here what I want to Update ID 2 to 1 Most of action on Table1,Table2 and Table3 are Update No Insert... I am merging data...
What is it about the update to Table1 that makes you want to INSERT a new row into Table2?
Please let me know if u need more information. Thank You.
December 24, 2013 at 8:25 pm
I'm afraid your response still leaves a lot of open questions, so I guess I'm just going to need to give you a general answer.
When you do the first UPDATE on Table1, you can INSERT the results (updated rows) into a temporary table using an OUTPUT clause. Probably using that you can determine if there are any matching rows (e.g., perhaps the criteria is firstname, lastname, dob) in Table1 that have some attribute in Table2 that you want to UPDATE.
Again, though you're not clear on if/whether any of the data in Table3 must be changed as a result of the initial update.
The biggest question of all though, is probably why do you have duplicates in Table1 in the first place? They make your subsequent steps really awkward.
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
December 24, 2013 at 8:33 pm
Thank You for your reply....
That's a bad data the user is entering and its hard for stop, that's why i am merging through first/last and DOB.
Could you please shoe me right path to how i can accomplish this logic in my SP? that would be big help for you. If you still not clear my question, I will more than happy to explain you...
BTW in Table3 i wanted to Update PID from TABLE2.PID..
Thank You.
December 24, 2013 at 8:40 pm
rocky_498 (12/24/2013)
That's a bad data the user is entering and its hard for stop, that's why i am merging through first/last and DOB.
One of the main points of an application front end is to stop bad data before it gets stored. My best suggestion is that you address the problem at the source and not try to correct it after the fact, otherwise the situation is just going to compound itself until it becomes impossible or at least extremely inefficient to fix.
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
December 24, 2013 at 8:48 pm
And saying that it is "hard to stop" in the front end is simply not true. Try running the following:
CREATE TABLE #Table1
(
ID NVARCHAR(50),
FName NVARCHAR(50),
LName NVARCHAR(50),
DOB NVARCHAR(50),
Active bit,
PRIMARY KEY (FName, LName, DOB)
);
--Insert Table1
Insert into #Table1 (ID,FNAME,LName,DOB)
VALUES ('1','Smith','Tom','12/12/2011');
Insert into #Table1 (ID,FNAME,LName,DOB)
VALUES ('2','Smith','Tom','12/12/2011');
GO
DROP TABLE #Table1;
The error thrown by the second INSERT is simple to trap in the front end and prevent the capture of duplicates.
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
December 24, 2013 at 8:50 pm
pietlinden (12/23/2013)
Your field sizes and some of the types are a bit over the top.DOB NVARCHAR(50)? Makes doing date math really hard.
ID NVARCHAR(50)? Can't you use an INT IDENTITY?
same for Phone
Note that I also agree with this. For the record, DOB should be DATE data type.
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
December 24, 2013 at 8:50 pm
I am 100% agree what you are saying, however its really hard for us to fix this problem, due to lot of restrictions or human entries...
Any Code advise for me? so i finish this task ASAP..
December 24, 2013 at 8:57 pm
rocky_498 (12/24/2013)
I am 100% agree what you are saying, however its really hard for us to fix this problem, due to lot of restrictions or human entries...Any Code advise for me? so i finish this task ASAP..
Any code I give you directed at your initial question would simply be enabling the continuation of a really bad design and I'd prefer not to be an enabler in this case.
A better question (that I would be willing to answer): How can I clean up the data in my tables so that I no longer have duplicates in Table1 and I redirect all the errant PIDs in the later tables to the one non-duplicate entry I save for each person? Now that is something that make sense for me to answer.
But I (or anyone else jumping in to answer this) would need a whole lot more sample data to make a reasonable effort at some clean up scripts.
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
December 24, 2013 at 8:58 pm
Yes DOB in my DB is DATETIME, In my sample data i created, i am using varchar for DOB. I will change it to datetime for DOB....
Like i Said, I know we can create few logic to stop duplicate insert... You know name is not a good to use Primary key, it could possible one user enter Last Name = Junior AND other user Last Name = Jr. (We are getting there)
If we merge or user can merge to find out which one is real duplicate that would be solution for now...
Any help in code?
December 24, 2013 at 9:16 pm
rocky_498 (12/24/2013)
Yes DOB in my DB is DATETIME, In my sample data i created, i am using varchar for DOB. I will change it to datetime for DOB....
Why do you need the TIME part of DATETIME for a DOB? My suggestion was to use DATE data type.
rocky_498 (12/24/2013)
Like i Said, I know we can create few logic to stop duplicate insert... You know name is not a good to use Primary key, it could possible one user enter Last Name = Junior AND other user Last Name = Jr. (We are getting there)
There are other ways to do the same thing. PRIMARY KEY was just an easy demonstration. If you know how to create the logic to prevent duplicates, then why not do so? In the end, you'll be saving yourself a lot more headaches than the time it would take to fix it.
Note that I am not trying to be a hard-@*** here. I'm assuming you came to this forum seeking the help of experts. While I don't consider myself to be an expert, I think the advice I'm giving you is sound. If you ignore it, don't you defeat the very purpose you had in seeking help here in the first place?
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply