November 18, 2016 at 1:36 pm
how to search records in a table and identify which records do not exist based on 2 conditions and then insert the missing records from another table
November 18, 2016 at 1:51 pm
Merge
or
Insert Table1 (columns)
select columns
from table2
where not exists (select 1 from etc. . .)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 18, 2016 at 1:57 pm
for example I have table 1 with 3 columns and table 2 with 2 columns I have ID column in both tables and a desc column in both tables. No I need to add rows to table 1 so that each ID has the same number of desc. how do I search table 1 for objects that are not already in a certain ID and not another. and then insert that ID with a desc
November 18, 2016 at 2:03 pm
Please read the article contained in the link in the signature line. It will show you how to post DDL and data so that we are not guessing as to how your tables are defined, what keys you have, which are unique, etc. . .
The question you are asking seems fairly straightforward. But without DDL, it is just guessing.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 18, 2016 at 2:59 pm
unfourtunatly i am unable to post this information... but hope this helps...
table1
(
ID CHAR(10)
SUB_ID CHAR(3)
DESC CHAR(100)
)
table2
(
ID CHAR(10)
DESC CHAR(100)
)
I would need to look through table 1 so that for every SUB_ID has the same values for each SUB_ID in table 1. I need to look at table2 and insert rows into table 1 for the SUB_ID that are missing from each SUB_ID
For example this is the look of the current table...
table1
12345 ABC TEXT
12345 ABC TEXT1
12345 XYZ TEXT
I need to the final table to look like this....
12345 ABC TEXT
12345 ABC TEXT1
12345 XYZ TEXT
12345 XYZ TEXT1
November 18, 2016 at 3:21 pm
knight944 (11/18/2016)
unfourtunatly i am unable to post this information... but hope this helps...table1
(
ID CHAR(10)
SUB_ID CHAR(3)
DESC CHAR(100)
)
table2
(
ID CHAR(10)
DESC CHAR(100)
)
I would need to look through table 1 so that for every SUB_ID has the same values for each SUB_ID in table 1. I need to look at table2 and insert rows into table 1 for the SUB_ID that are missing from each SUB_ID
For example this is the look of the current table...
table1
12345 ABC TEXT
12345 ABC TEXT1
12345 XYZ TEXT
I need to the final table to look like this....
12345 ABC TEXT
12345 ABC TEXT1
12345 XYZ TEXT
12345 XYZ TEXT1
This is getting closer.
Please explain this statement:
I would need to look through table 1 so that for every SUB_ID has the same values for each SUB_ID in table 1.
What data is suppose to be in table2?
Even though you can't post "real" data, you can post data such that it is easily consumable like I did with your data for table1.
create table #table1
(
ID CHAR(10),
SUB_ID CHAR(3),
TDESC CHAR(100)
)
insert #table1 (ID, SUB_ID, TDESC) VALUES
('12345', 'ABC', 'TEXT'),
('12345', 'ABC', 'TEXT1'),
('12345', 'XYZ', 'TEXT')
create table #table2
(
ID CHAR(10),
TDESC CHAR(100)
)
insert #table2 (ID, TDESC) VALUES
('12345', 'text'),
('12345', 'text1'),
('12345', 'text2')
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 18, 2016 at 3:35 pm
table2 contains the distinct value of each ID's desc. for example....
to clarify, i would need to compare table1 to table2, look through table1 ID's and for each ID insert a row from the values of table 2. Each SUB_ID for each ID has to have the same amount of descriptions for each SUB_ID of the ID.
12345 text
12345 test1
12345 text2
Hope this helps. It hard for me to explain, but hope this time I got it.
THANK YOU FOR YOUR HELP!
November 18, 2016 at 7:50 pm
I have edited my earlier post to include create and insert statements for table2.
I must be dense, but your explanation and your example output do not make sense to me. What I think you are asking for is a cross apply.
select distinct t1.id, t1.sub_ID, tt2.tdesc
from #table1 t1
cross apply
(
select t2.id, t2.tdesc from #table2 t2
) tt2
order by t1.sub_id
Give this a try. See if it gets you started in the direction you are looking for.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 18, 2016 at 8:06 pm
Thank you for your help! I will give it a try and see how it goes. If anything I will try to figure out a way to better explain myself.
November 19, 2016 at 4:08 pm
I will try to clarify my question.
So I have two tables with the following sample data....
table1
12345 ABC TEXT
12345 ABC TEXT1
12345 XYZ TEXT
table2 contains distinct values for the desc column along with the ID from table1...
12345 text
12345 test1
I need to look through table one and identify which desc is missing from each subcategory. For each distinct ID in the table each sub_id needs to have the same number of entries. table1 final should look like this....
TABLE1 FINAL :
12345 ABC TEXT
12345 ABC TEXT1
12345 XYZ TEXT
12345 XYZ TEXT1
November 19, 2016 at 4:57 pm
Either you have something wrong in your data or T2 is irrelevant to the question.
Here's my solution with the sample data included. Note that the second table you provide is not necessary to come up with a solution:
create table #table1
(
ID CHAR(10),
SUB_ID CHAR(3),
TDESC CHAR(100)
);
create table #table2
(
ID CHAR(10),
TDESC CHAR(100)
);
GO
insert into #table1 (ID, SUB_ID, TDESC) VALUES
('12345', 'ABC', 'TEXT'),
('12345', 'ABC', 'TEXT1'),
('12345', 'XYZ', 'TEXT');
insert into #table2 (ID, TDESC) VALUES
('12345', 'text'),
('12345', 'text1'),
('12345', 'text2');
SELECT DISTINCT t1.ID
, t1.SUB_ID
, t2.TDesc
FROM #table1 t1
CROSS JOIN
(
SELECT DISTINCT TDesc
FROM #table1) t2;
Note that I am joining a subset of #table1 to itself ... never used #table2
November 19, 2016 at 5:02 pm
Thank you! Looks like this worked!
November 20, 2016 at 10:42 am
I have a new twist...
If my table1 now looks like this..
create table #table1
(
ID CHAR(10),
SUB_ID CHAR(3),
TDESC CHAR(100)
PCT char(3)
)
I want to still search through table 1 and identify which desc is missing from each subcategory. For each distinct ID in the table each sub_id needs to have the same number of entries. But now i have a forth column which includes integers. For those columns that are added from the cross join I want to update those added columns to be filled in with a '0'.
Is this possible? Thanks again for your help, it is greatly appreciated!!
November 21, 2016 at 6:11 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply