2

im new on SQL and trying to create a table based on two tables. I have two tables with 7 columns each one some registers had incomplete information BUT i need to filter this.

Conditions. -cannot have duplicates on ID_1 on result table. "easy i think use distinct and i have the non duplicated" -cannot have duplicates on ID's 1-2. But if i have one i need to copy the row that contain more information.

I will attach the information about task and my code that is newbie at all

    create database Task23

    go

    USE Task23

    create table table_entities
(
entityID_1 varchar(max),
entityID_2 varchar(max),
dateAdded varchar(max),
entityName varchar(max),
entityWebsite varchar(max),
entityIndustry varchar(max),
entityCountry varchar(max),
)

create table table_entities_NEW
(

entityID_1 varchar(max),
entityID_2 varchar(max),
dateAdded varchar(max),
entityName varchar(max),
entityWebsite varchar(max),
entityIndustry varchar(max),
entityCountry varchar(max),
)

create table table_entities_RESULT
(

entityID_1 varchar(max),
entityID_2 varchar(max),
dateAdded varchar(max),
entityName varchar(max),
entityWebsite varchar(max),
entityIndustry varchar(max),
entityCountry varchar(max),
)

go
create table table_entities_full
(

entityID_1 varchar(max),
entityID_2 varchar(max),
dateAdded varchar(max),
entityName varchar(max),
entityWebsite varchar(max),
entityIndustry varchar(max),
entityCountry varchar(max),
)
go



insert into table_entities values ('e113942','processndata','1504167890','Process and Data','http://yadawebsite.com','Industrial Automation','USA');
insert into table_entities values ('collectivedata' ,'', '1504165693' , 'Collective Data' , 'http://www.collectivedata.com' , 'IT' , 'USA');
insert into table_entities values ('bloodsense','e109984','1504165620','BloodSense','http://just asample.com','Medical Devices','USA');
insert into table_entities values ('e117385','','','TrendHealth Ltd','','Healthcare','');
insert into table_entities values ('mando','','','Mando Health','','Healthcare','');

go


insert into table_entities_NEW values ('e117385','trendhealth','1504167890','TrendHealth Ltd','http://samplewebsite.com','Healthcare','UK');
insert into table_entities_NEW values ('trendhealth' ,'', '' , 'TrendHealth Ltd' , '' , '' , 'UK');
insert into table_entities_NEW values ('mando','e93344','1504117360','Long View Med','http://just asample.com','Healthcare','USA');
insert into table_entities_NEW values ('e112481','','','Long View Med','','Medical Devices','');
insert into table_entities_NEW values ('e112481','','','Long View Med','','Medical Devices','');
insert into table_entities_NEW values ('trendhealth','','','TrendHealth Ltd','','','');
insert into table_entities_NEW values ('microsense','','','MicroSense LLC','','','USA');
insert into table_entities_NEW values ('e114958','','','Telemed','','Medical Devices','');




select * from table_entities;
select * from table_entities_NEW;
select * from table_entities_RESULT;


insert into table_entities_RESULT select distinct * from table_entities
insert into table_entities_RESULT select distinct * from table_entities_new

So thats the first problem when i insert i had one duplicate because compare all registers in file and dont fill like i wish.

Click here

The expected output is THIS:

insert into table_entities_RESULT values ('e113942','processndata','1504167890','Process and Data','http://yadawebsite.com','Industrial Automation','USA');
insert into table_entities_RESULT values ('collectivedata' ,'', '1504165693' , 'Collective Data' , 'http://www.collectivedata.com' , 'IT' , 'USA');
insert into table_entities_RESULT values ('bloodsense','e109984','1504165620','BloodSense','http://just asample.com','Medical Devices','USA');
insert into table_entities_RESULT values ('e117385','','','TrendHealth Ltd','','Healthcare','');
insert into table_entities_RESULT values ('mando','','','Mando Health','','Healthcare','');
insert into table_entities_RESULT values ('e112481','','','Long View Med','','Medical Devices','');
insert into table_entities_RESULT values ('microsense','','','MicroSense LLC','','','USA');
insert into table_entities_RESULT values ('e114958','','','Telemed','','Medical Devices','');evices','');

thanks for try to understand a new mate on this. Regards.

LDRNSJ
  • 21
  • 4
  • Your code work without errors – Ruslan K. Sep 11 '17 at 16:03
  • 1
    Excellent job post sample data and tables. I wish everyone would be so thorough. However, the code you posted works just fine. But I suspect it is not returning what you want. Can you explain what you expect for output? And no idea what your link is to. – Sean Lange Sep 11 '17 at 16:07
  • Wow. Finally a first post complete with data! – Eli Sep 11 '17 at 16:23
  • I think you can do this by using MERGE operator provide in sql server. please refer below link for more detail - https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/ – Rahul Richhariya Sep 11 '17 at 16:54
  • Hello, in the link "here" <> have a .doc with the expected output file. And the same explain. – LDRNSJ Sep 11 '17 at 18:41
  • @SeanLange I updated the file and put the expected that i trying to get but with my two lines i just have a little peace of the result. Thanks for Give me time insert into table_entities_RESULT select distinct * from table_entities insert into table_entities_RESULT select distinct * from table_entities_new – LDRNSJ Sep 11 '17 at 18:53

0 Answers0