SQL Server 2014 :: Merge Similar Records From Excel?
May 19, 2015
I am working on a project that will require me to get a flat data file (excel spreadsheet) with hundreds of thousands of records. Each record is an Owner, and specifically what they own. There will be a field for OwnerName that I want to figure out a way to pull the data into a database like;
Table(Owners) - make sure owner is listed only once
Table(Properties) - joined to owners showing all properties that person owns
Now the tricky part, the owner names might not be exactly the same. Some records might have;
Smith, John
John Smith
Smith, John T
etc.
To make matters worse, this will be a continuous process. I will receive updated excel spreadsheets from time to time and will need to import the new records, many times overwriting the old data. For the good news, there should be an OwnerID that will be unique within the excel data. So as I am merging similar records into the Owners table, I should have a list of OwnerID's that can forever be used to link to the owner.
View 3 Replies
ADVERTISEMENT
Nov 20, 2012
I have the below in a table - TABLEA
Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,240.0
01117,2012-01-02, 541,BASIC,105.0
And I am trying to insert this into another table TABLEB but it wont allow as I am getting a duplicates error because of the unique Indexing on the table.
Ref,Date,Time,Code
Ideally if I could run a query on TABLEA so that it would merge and sum the minutes where REF,Date,TIME,CODE are the same.
i.e., the above would become
Ref , Date , TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,345.0
Is this possible?
Another option that would work for me is the TIME column info isnt required to remain at 541.
If there was a count increment on the rows it would allow the import to rum.
ie if the above became
Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 1,BASIC,240.0
01117,2012-01-02 ,2BASIC,105.0
It would also import correctly.
The first option is which I would prefer.
View 2 Replies
View Related
Sep 22, 2015
create table #t1 (id int)
create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
View 3 Replies
View Related
Aug 18, 2015
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018
The query I am using
select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'
View 3 Replies
View Related
Jun 17, 2015
I have a table which contains single legs. E.g.
Row Leg-ID From To On DateFrom DateTo DOW
1 ABC123 AAA BBB CCC 01JAN15 01JAN15 1
2 ABC123 AAA BBB CCC 07JAN15 07JAN15 1
3 ABC123 AAA BBB CCC 14JAN15 14JAN15 1
4 ABC123 XXX YYY ZZZ 21JAN15 21JAN15 1
I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:
Row Leg-ID
From To
On DateFrom
DateTo DOW
1 ABC123
AAA BBB
CCC 01JAN15
14JAN15 1
3 ABC123
XXX YYY
ZZZ 14JAN15
14JAN15 1
The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:
check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..
View 4 Replies
View Related
Oct 24, 2014
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
View 1 Replies
View Related
Oct 1, 2015
I want to count records which has almost similar name. Suppose record has company name like comcast and other record has company name like Comcast-xfinity, so how would I count this 2 records as Comcast?
View 4 Replies
View Related
Oct 8, 2007
Hi,
I have 4 tables :
Code Block
Create Table #Request ( [requestid] int , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #RequestDetail(reqdetailid int, [customername] Varchar(20), [customerage] int, requestid int);
Create Table #Call(Callid int,Calltype int,callDetailid int )
Create Table #CallDetail(callDetailId int,empid int)
Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)
Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)
Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','SA024',44,123,0);
Insert Into #request Values('22654392','SA023',12,134,0);
Insert into #requestdetail values(1, 'Bill',23,'324234')
Insert into #requestdetail values(2, 'Tom',25,'223452')
Insert into #requestdetail values(3, 'Bobby',27,'456456')
Insert into #requestdetail values(4, 'Guck',29,'22322362')
Insert into #requestdetail values(5, 'Bobby',33,'22654392')
1. My stored proc will take the CallDetailID.
2. I have to find out the requests made on this calldetail.
3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.
4. If found return the details of the original record :
[A similar requirement was solved earlier, but the structure has changed. This is a seperate requirement with different table strucure. Hence a new post. ]
thanks
View 7 Replies
View Related
Aug 11, 2015
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
View 9 Replies
View Related
Aug 25, 2015
have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container.
View 7 Replies
View Related
Mar 23, 2006
Hello,
I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged.
Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me.
Corey
View 7 Replies
View Related
Jul 28, 2015
I need to select records in column that start with '% ' -- %space e.g. column A = % mytest
I tried select column name with like operator but it select records who start with % and ignore space
select columnA from table where columna like '% %';
View 9 Replies
View Related
Jul 20, 2015
Currently i have set of queries which i run for data extraction and result pasted in Excel table so that my pivot table and Chart gets populated along with the summary in Excel file.
I would like to automate this thing and want to know if i can achieve excel output for dashboard on a click of button.
View 2 Replies
View Related
May 6, 2014
So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like:
SELECT *
FROM Orders
WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
View 9 Replies
View Related
Aug 6, 2014
I'm trying to do the following and haven't been able to figure it out.
Say there's a table with these records:
Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g
I want to generate a number that represents the groups of columns like this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3
So that each grouping gets its own identifier. I've tried this:
SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable
But I get this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2
View 9 Replies
View Related
Jul 31, 2014
I am trying to add multiple records to my table (insert/select).
INSERT INTO Users
( User_id ,
Name
)
SELECT ( SELECT MAX(User_id) + 1
FROM Users
) ,
Name
But I get the error:
Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.
But I am using the max User_id + 1, so it can't be duplicate
This would insert about 20 records.
Why the error?
View 7 Replies
View Related
Apr 13, 2015
I have a query question.
Consider a table with the following structure:
RecordID (PK - int) - RecordDate (DateTime)
I need to find all records that fall within a 7 day period slot based on the first RecordDate of a specific slot.
Example, consider the following records:
RecordID - RecordDate
1 - 2015-04-01 14:00
2 - 2015-04-03 15:00
3 - 2015-04-03 16:05
4 - 2015-04-03 19:23
5 - 2015-04-06 09:15
6 - 2015-04-06 11:30
7 - 2015-04-07 12:00
8 - 2015-04-09 15:15
The result of the query I'd like should look something like this
1
2
5
7
8
So basically I'd like to leave record 3 and 4 out because they fall within 24 hours of record 2 and I'd like to leave record 6 out because it falls within 24 hours of record 5.I'd tried working with a CTE and set a dateadd(d, 1, recorddate), join it on itself and use a between From / To filter on the join but that didn't work. I don't think NTILE will work with this?
View 9 Replies
View Related
May 19, 2015
I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several sql servers with 2014 edition on them, and I have the same issue on all of them. The query breaks off at different points on each sever - for example one of them breaks off at 209 records as i said above, another one at 307. The last record always gets truncated at the same place. The csv attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter.
What i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:
select col1, col2, col3
from table A1
where col1 > 1000 order by col1
View 2 Replies
View Related
Jul 6, 2015
I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.
Say for example, following is the list of records I have in my table which I want to group into 5 batches
A123
A124
A124
A123
A127
After Ntile I will get the below,
Desired output is, Need output like Ntile but all same id should reside in single batch
Even if I n=5, maximum possibility of batches are 3 only.
View 2 Replies
View Related
Oct 30, 2015
I have a table that includes the fields below:
ID - INT
Machine - TINYINT
StartTime - DATETIME
EndTime - DATETIME
What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:
ID - 1
Machine - 2
StartTime - 2015-09-01 22:00:00.000
EndTime - 2015-09-03 22:00:00.000
So what I am looking for is taking the above record and turning it into 3 records like below:
ID Machine StartTime EndTime
1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999
1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999
1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000
View 5 Replies
View Related
Oct 29, 2013
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
View 8 Replies
View Related
May 14, 2015
I have installed SQL 2014 (Evaluation Version) on testing machine. We want to import some excel files on database. I manually created one Test Database and now trying to import excel file. Import completed successfully but I am not able to see any table created as result of Import. I tried it 3-4 times and even restarted sql services but no luck.
View 3 Replies
View Related
Jul 16, 2014
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
View 9 Replies
View Related
Aug 18, 2014
I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
View 1 Replies
View Related
Jan 24, 2015
I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads from B can be used for A and leads from A can be used for B, so I want to merge the data.
Example:
Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B
727-555-1234 Dave Thomas Gieco
I would like the end result to be one record:
Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B Gieco
Since these were imported into SQL they all have a unique ID, here are the current labels
ID,phone_ number,first_ name,last_name,address1, address2, address3,city,state,postal_code,HOME_OWNR,HH_INCOME,CREDIT_RATING,AGE,MATCH,source_id,
title,comments,dnc_flag,provider,vehicle,coverage,alt_phone,email,marital status,dob
View 8 Replies
View Related
Sep 30, 2015
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
[Code] ....
View 4 Replies
View Related
May 27, 2014
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies
View Related
Aug 5, 2015
I am trying to load data from Excel 2007 version into SQL server 2014 DB. I am getting below error" SSIS Error Code DTS_E_CANNOTACQUIRE CONNECTION FROM CONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed".I have tried all options like changing Delay Validation is TRUE and in properties i changed runtime 64 bit to FALSE but still getting above error.
View 3 Replies
View Related
Aug 5, 2015
I have one excel sheet contains 50 sub sheets with different names on it. Is it possible can i load all sheets into SQL using SSIS?
View 2 Replies
View Related
Mar 5, 2015
I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
View 2 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
View 3 Replies
View Related
Jul 20, 2005
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
