May 21, 2012 at 1:11 pm
Hello Everyone,
Here is the DDL, again sorry which is exactly the same that was posted except there is an id for both customer and person, and its the identifier and unique:
create table #Customers
(
id int,
firstname nvarchar (200),
lastname nvarchar(200),
address nvarchar(200)
)
insert #Customers
select 'bob', 'sam', '39919 go figure rd.' union all
select 'billy', 'bob', '' union all
select 'billy', 'smith', '' union all
select 'billy', 'SomeLastName', '' union all
select 'jack', 'skeleton', ''
create table #Person
(
id int
firstname nvarchar (200),
lastname nvarchar(200),
address nvarchar(200)
)
insert #Person
select 'bob', 'davis', '39 santa ana rd.' union all
select 'billy', 'bob', '' union all
select 'billy', 'smith', '' union all
select 'billy', 'SomeLastName', '' union all
select 'brandon', 'rex', '' union all
select 'peter', 'skeleton', ''
May 21, 2012 at 1:13 pm
Once you post the DDL (CREATE TABLE statement) for your tables, sample data (INSERT INTO statements, about 6 to 10 rows of data) for the tables, and the expected results based on the sample data you will get the help you are requesting. Until then, we really can't do much other than guess at what you need.
One thing you will find is that we will try to find you a set-based solution not a cursor-based or loop-based solution. A set-based solution will be faster and more scalable than the cursor-based solution.
May 21, 2012 at 1:14 pm
What is your expected output based on the sample data?
May 21, 2012 at 1:14 pm
Better, we now have 2 out of 3 things we need. We have ddl and sample data. What is the desired output from this sample data. You said something about another table? Help us help you. If I was a consultant I would like this because my billable hours would be skyrocketing, however I am a volunteer on here and the amount of time I am willing to donate is running short of 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/
May 21, 2012 at 1:19 pm
By the way, the code you posted above has a few issues. You really need to test your code before posting it. Here is what I have after fixing those issues:
create table #Customers
(
id INT IDENTITY(1,1),
firstname nvarchar (200),
lastname nvarchar(200),
address nvarchar(200)
);
insert #Customers
select 'bob', 'sam', '39919 go figure rd.' union all
select 'billy', 'bob', '' union all
select 'billy', 'smith', '' union all
select 'billy', 'SomeLastName', '' union all
select 'jack', 'skeleton', '';
create table #Person
(
id int IDENTITY(1,1),
firstname nvarchar (200),
lastname nvarchar(200),
address nvarchar(200)
);
insert #Person
select 'bob', 'davis', '39 santa ana rd.' union all
select 'billy', 'bob', '' union all
select 'billy', 'smith', '' union all
select 'billy', 'SomeLastName', '' union all
select 'brandon', 'rex', '' union all
select 'peter', 'skeleton', '' ;
SELECT * FROM #Customers;
SELECT * FROM #Person;
DROP TABLE #Customers;
DROP TABLE #Person;
May 21, 2012 at 1:22 pm
Again thank you, the output I would like is if bob customer id 1 from customer table matches the first name bob from person table with id 27, then insert into table match
the table is structured like this:
CREATE TABLE table_name
(
customerid int identity (1,1),
personid int,
type varchar (50),
)
so in the match table it inserts the ids of both matching from customer and person table, the type tells if its first name match, last name or address, I am probably going to change it to an int as well so i can just reference it by a number which may be easier plus better performance. let me know if you agree with that.
thank you in advanced.
May 21, 2012 at 1:25 pm
Siten0308 (5/21/2012)
Again thank you, the output I would like is if bob customer id 1 from customer table matches the first name bob from person table with id 27, then insert into table matchthe table is structured like this:
CREATE TABLE table_name
(
customerid int identity (1,1),
personid int,
type varchar (50),
)
so in the match table it inserts the ids of both matching from customer and person table, the type tells if its first name match, last name or address, I am probably going to change it to an int as well so i can just reference it by a number which may be easier plus better performance. let me know if you agree with that.
thank you in advanced.
Please don't just describe what should be inserted into the destination table, show us what should be there based on the sample data. A picture is worth a thousand words, and it helps eliminate misunderstandings of what is said (usually).
May 21, 2012 at 1:32 pm
ok then....
like this:
if firstname from customer where customerid = 1 matches firstname from person where personid = ... whatever id
THEN
INSERT INTO match (customerid, personid, columnname)
VALUES (customerid, personid, firstname)
then after the first customerid 1 is done, then increment to the next number which is 2, so customerid firstname will check to see if it matches with anyone on person table etc. etc.
hope this is exactly what you are looking for.
Thank you
May 21, 2012 at 1:41 pm
No what we keep asking for is for you to tell us what those values are. Not pseudocode, not an explanation of how to do it. Notice how the sample was hardcoded? Basically the same thing for your desired output. That way it is 100% clear exactly what you are looking for. Again I would ask that you read the best practices link. It will take less than 5 minutes of your time and will shed some insight into the challenges faced by people on this side your problem.
_______________________________________________________________
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/
May 21, 2012 at 2:05 pm
Hello hello SSCarpal Tunnel
thank you for being paitent with me, here is the information that hopefully makes sense and I included everything from all tables to the query i amt rying so everything is right there including indenting things so its in order, which hopefully might bring the post to a close and answer to my problem and hopefully anyone else out there that may experience this same issue:
Here is everything, hopefully covers everything going back from customer and person table to adding match table and also adding what i currently have tried script.
create table #Customers
(
id INT IDENTITY(1,1),
firstname nvarchar (200),
lastname nvarchar(200),
address nvarchar(200)
);
insert #Customers
select 'bob', 'sam', '39919 go figure rd.' union all
select 'billy', 'bob', '' union all
select 'billy', 'smith', '' union all
select 'billy', 'SomeLastName', '' union all
select 'jack', 'skeleton', '';
create table #Person
(
id int IDENTITY(1,1),
firstname nvarchar (200),
lastname nvarchar(200),
address nvarchar(200)
);
insert #Person
select 'bob', 'davis', '39 santa ana rd.' union all
select 'billy', 'bob', '' union all
select 'billy', 'smith', '' union all
select 'billy', 'SomeLastName', '' union all
select 'brandon', 'rex', '' union all
select 'peter', 'skeleton', '' ;
CREATE TABLE Match
(
id int identity (1,1),
customerid int,
personid int,
type varchar (200)
)
below is what I have done so far:
declare
@firstname nvarchar(150), @lastname nvarchar (200), @address nvarchar(250),@max int,
@maxperson int , @counterpersontable int, @counter nvarchar (250)
select @max-2 = COUNT(*) from CurrentCustomers
set @counter = 1
while (@counter < @max-2)
BEGIN
select @firstname = firstname from Customers where customerID = @counter
select @maxperson = COUNT(*) from person
set @counterpersontable = 1
while(@counterpersontable < @maxperson)
BEGIN
if exists(select FirstName from Person where ProfileID = @counterpersontable AND FirstName = @firstname)
BEGIN
insert into Match(customerid, personid, columnid)
select @counter, @counterpersontable, 1
from Person a
END
ELSE
BEGIN
Update Match
set customerid = @counter, personid = @counterpersontable , columnid = 1
where customerid = @counter and personid = @counterpersontable and columnid = 1
END
set @counterpersontable = @counterpersontable + 1
END
set @counter = @counter + 1
END
May 21, 2012 at 2:09 pm
hehe you are pretty close. Thanks for recapping all the scripts and such that will certainly help. If you just create inserts for your match table based on the sample data we can knock this out pretty easily.
Just fyi, my name is Sean, SSC Carpal Tunnel is just my forum handle. Look at yours and you will see your name is above there a little bit. It is something that nearly everybody misses. 😉
_______________________________________________________________
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/
May 21, 2012 at 2:15 pm
I see now says the blind man 😀
insert #Match
select '1', '27', 'firstname'
select '8', '6', 'firstname'
select '40', '107', 'address'
select '45', '19', 'firstname'
May 21, 2012 at 2:26 pm
Again, make sure the code you post works as expected. Problem with the code for populating #Match.
Also, the results have no correlation to the sample data that is inserted into the first two tables.
Please base the expected results on what you are providing as sample data. The key here is that the id columns will have id values between 1 to 5 and 1 to 6 respectively. This doesn't match your expected results at all.
May 21, 2012 at 2:31 pm
here go, this works:
insert Match
select '1', '1', 'firstname'
select '1', '6', 'firstname'
select '4', '2', 'address'
select '5', '1', 'firstname'
sorry for the confusion I put a # sign before matching thinking it was a temp table when it isnt.
May 21, 2012 at 2:41 pm
Siten0308 (5/21/2012)
here go, this works:
insert Match
select '1', '1', 'firstname'
select '1', '6', 'firstname'
select '4', '2', 'address'
select '5', '1', 'firstname'
sorry for the confusion I put a # sign before matching thinking it was a temp table when it isnt.
Actually, this still doesn't work as expected as it only inserts one row of data into #Match.
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply