T-SQL (SS2K8) :: Unable To Extract Data Based On Matching Info From A Local Database
Oct 2, 2014
I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,
I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..
Is there a simple tweak that would speed it up?
USE [CAS]
GO
DECLARE @PhoneDB varchar(max),
@SQL NVARCHAR(MAX);
set @PhoneDB = '[PHONEDB_PHI]';
set @SQL = 'SELECT
[Code]....
View 4 Replies
ADVERTISEMENT
Sep 3, 2014
I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
View 5 Replies
View Related
Aug 11, 2014
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
View 9 Replies
View Related
Apr 28, 2015
I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000
Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900
create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)
[Code] ....
View 5 Replies
View Related
Jul 16, 2015
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact];
CREATE TABLE [dbo].[SourceContact]
(
[SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED,
[ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''),
[ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
[code]....
View 2 Replies
View Related
Oct 31, 2007
Hi all,
I've created a database named HyperCodexDB.mdf and I'm trying to extract some information from, I've no error, but the code after Open() method is even not executed:
Here is the Web.Config
and in the .cs file :
Hashtable names = new Hashtable();
int i = 0;
string conn_str = ConfigurationManager.ConnectionStrings["hcConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(conn_str);
SqlCommand cmd;
cmd = new SqlCommand("SELECT Nom FROM TableRecueil", con);
SqlDataReader rdr = null;
try
{
Label1.Text = "Hello"; ////////????Here changes the value of the label
con.Open();
Label1.Text = "Bye"; ////////????But here the value of Label1.Text remains "Hello"
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
names.Add(i, rdr["Nom"]);
i++;
}
Label1.Text = names[0].ToString();
}
catch (SqlException ex){ }
finally
{if (rdr != null)
{rdr.Close();}
if (con != null)
{con.Close();}}
Thanks in advance
Ali
View 8 Replies
View Related
Jun 25, 2014
What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.
Here is the link : [URL] ....
But I am getting Error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()
[Code] ....
View 4 Replies
View Related
Sep 17, 2015
I have two tables in SQL Server--an expression table and a translate table.
DECLARE @EXPRTABLE TABLE
(
expression varchar(100)
)
INSERT INTO @EXPRTABLE
SELECT 'CALL("-","","TEST")'
[Code] ....
I am trying to (for lack of a better way to explain it), overlay the pattern onto the expression and extract the text where the "#" symbols are.
So,
CALL("-","","TEST") using pattern CALL("#","#","#") would return -,,TEST
SUB(2,67,TEST,4,) using pattern SUB(2,#,TEST,4,) would return 67
View 8 Replies
View Related
Oct 6, 2015
Consider the following: I have a table, say ORDERS, with these entries -
CustID
ProductID
1 CAN
2 2
3 1,2
4 4
5 1,2,3,4,5,CAN
6 10
7 CAN
8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
View 12 Replies
View Related
Sep 27, 2007
I saw a video on how its possible to create a local database so that when the application is being deployed, the mdf file will be deployed with it and will contain the enter data. The instructor(Beth Messi) showed that all I need to do is to add the .mdf file using the "Rightclick Project name in solution explorer > select add > add new item > then in the dialog that opens, select SQL database and click ok. I did this but the Visual Studio kept saying: "An error occored while extablishing a connection with the server. When connecting SQL Server 2005, this failure may have been caused by the fact that under the default settings of SQL Server does not allow remote connections. (Provider: Shared memory provider, error: 40 - could not open a connection to SQL Server)"
Honestly, I don't know how to go futher with this. The SQL server am using is the professional edition and I have been able to use it through Visual Studio to create Databases. It connects alright in that senario. Please I really need help here.
View 1 Replies
View Related
May 28, 2014
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
View 4 Replies
View Related
Nov 17, 2014
I have 57 tables, 7 views and 1 stored procedure. Just wanted know based on these requirements how can I find the size of the database. Though the DB contains lots of tables, views and procedures. I am moving these details to new DB server. So I need to put right requirements.
View 2 Replies
View Related
Mar 13, 2014
To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.
The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.
The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.
Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style
This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.
Here is some sample data
USE FF_Winning_Together;
IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL
DROP TABLE #AmendTest;
CREATE TABLE #AmendTest
(
AmendmentIDINT IDENTITY(1,1)NOT NULL,
StyleCHAR(1)NOT NULL,
AmendmentStatusVARCHAR(10)NOT NULL,
DTDATETIMENOT NULL
[code]....
View 7 Replies
View Related
Mar 15, 2014
-- My first Data
create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')
[code]....
View 1 Replies
View Related
Jun 4, 2014
I am trying to pivot data based on columns value in year column... but results are not showing up correctly. I want to see all columns after pivot.I want to Pivot based on year shown in the data but it can be dynamic as year can go for last 3 years
I am also using an inner join as i have two amount columns in my code and i want to show both amount columns for all displayed year.I am able to pivot but I need in output all the columns like this Id,MainDate, Year1,Year2,Year3(if any), AMT1 for YR1, AMT2 for Yr1, , AMT1 for YR2, AMT2 for Yr2, AMT1 for YR3, AMT2 for Yr3,
Here is some data:
-- CREATE TABLE [dbo].[TEMP](
--[FileType] [varchar](19) NOT NULL,
--[dType] [char](2) NOT NULL,
--[dVersion] [char](2) NOT NULL,
--[Id] [char](25) NOT NULL,
--[MainDate] [char](40) NULL,
[code]....
View 5 Replies
View Related
Aug 11, 2014
i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.
We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.
EG Existing groups :
GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998
If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.
Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')
Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)
--Existing tasks
Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)
-- New tasks
Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')
This gives us 6 unassigned tasks and a uneven group assignment
GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2
This means the new tasks will be assigned like this
TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3
View 5 Replies
View Related
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
Aug 22, 2014
This is my code, table and result,
declare @t_JobNoticeID table (cvid int, JobNoticeID int)
insert into @t_JobNoticeID values(2456, 24);
insert into @t_JobNoticeID values(4000, 124);
insert into @t_JobNoticeID values(245, 9);
insert into @t_JobNoticeID values(2456, 19);
insert into @t_JobNoticeID values(4000, 904);
insert into @t_JobNoticeID values(4000, 11);
insert into @t_JobNoticeID values(24, 19);
[Code] ....
My question is -
How update statement looks like compare RESULT A1 with x_JobMatching?
So, my FINAL RESULT shown as follow,
CVIDJobNoticeIDisMatch
1925450
6590690
459130
2456191
4569110
40009041
24560
900240
24191
View 5 Replies
View Related
Jul 20, 2005
Hi,Anybody have an idea of copying data from tables of a database to anotherdatabase. It should be a choice to select all tables, single table orseveral tables.For them knowing Oracle it is possible to do it with 'exp', where you canchoose to script the database with or without data. So I am trying to getalike to MSSQL. The job is to unload data from a database with onestructure to another database with another structure.Thanks in advanceBest regardsTom Frank
View 4 Replies
View Related
May 20, 2014
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
View 4 Replies
View Related
Apr 9, 2015
I'm working on a join between two tables where I only want one row returned... I'm matching on two columns between two tables. One of those columns in the target table could be null. I only want one record returned.
create table #vehicle(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1))
create table #class(id int, classtype varchar(1), value int)
insert into #vehicle values(1, 'AUDI', 'R8', 'A')
insert into #vehicle values(2, 'AUDI', null, 'B')
insert into #class values(1, 'A', 100)
insert into #class values(2, 'B', 1)
[Code] ....
Using the above example, if VehicleModel is anything other than 'R8' is specified then I want it to return the other class type record.
This is going to be used as a join within a bigger statement, so I'm not sure ordering and returning top 1 is going to work.
View 9 Replies
View Related
Apr 2, 2015
I have tables in my database, tblNames1, tblNames2, tblNames3 for ex, and for addresses tblAddresses, can I match the address IDs in the names tables to the address table based on the values of a field? In this case CompanyName. Can they be matched automatically? My issue is that I have a huge CSV file with all the address information that needs to be assigned a foreign key based on the company name field which is also present in all the names tables, all the names are normalized.
View 1 Replies
View Related
Sep 29, 2014
I'm trying to extract data from our database for the number of phone calls our reps are doing.
In counting the calls I only want to include up to 3 calls to the same customer (field name is CompanyID) per day - anything more than this is ignored.
The query at the moment is something like:
SELECT COUNT(CallID) AS CallCount FROM Sales_Calls WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' AND RepID = 1
Using MSSQL 2012.
View 13 Replies
View Related
Sep 25, 2007
I have to extract data from 5 different oracle databasee with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies
View Related
Sep 25, 2007
I have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies
View Related
Feb 27, 2007
Hi,
My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.
I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.
This is my sql command:
select count(FingerPrintID) as FingerPrint
from Employee
Result Set is set up like this:
Result Name: FingerPrint ; Variable Name: User:: varDataset
Here is the error I get:
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".
My variable is set up as a Int16.
Please help!!!!!
If you could provide step by step example's that would really make my day.
Thanks,
Lawrence
View 7 Replies
View Related
Sep 25, 2007
Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.
There is a table which list all the 10 databases.
View 2 Replies
View Related
Mar 6, 2008
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies
View Related
Mar 10, 2014
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
View 2 Replies
View Related
Apr 8, 2008
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT A
22 MAIN C
22 INT A
I want the result to be:
ID LOC CODE
-- ----- ------
11 MAIN B
11 INT IB
22 MAIN C
22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S
SET s.code = B.code
FROM tbltest B
LEFT OUTER JOIN tbltest S ON B.id = S.id
WHERE (S.loc = 'INT')
But when I run it, it says "0 rows affected".
View 5 Replies
View Related
Oct 31, 2006
Good afternoon SQL dudes Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational Pick nested relational multi-valued relational database) into a SQL Server?More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system) I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience. Super duper, thank you SQL troopers :)
View 2 Replies
View Related
Mar 30, 2015
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table
[Code]....
View 2 Replies
View Related
Apr 2, 2015
I have tables in my database, tblNames1, tblNames2, tblNames3, and a main addresses table (currently empty). Once I've imported the address data I need to match the addressIDs in the names tables to the Primary ID in the address table based on the values of a field CompanyName (which is common to all the tables) My issue is that I have a huge CSV file with the master address information but obviously SQL server needs to assign foreign keys so the names tables can linked to corresponding rows in address table. It's a a many to 1 relationship as their will be one address with multiple name entries. All the names are normalized so everything can be matched up...
View 9 Replies
View Related
