Query With Data In Another Table...

Dec 12, 2007



Hi,

I have the following problem... For these two tables I have to query, all "nrtelefone" from PABX where tronco is not between tronco ini and tronco fim....and tronco is not in there clause "troncos_cliente", the the user will provide to us...
Let's suppose that the troncos_cliente has the value "NOT BETWEEN 32 and 55", and in tronco ini there is "10" and in tronco fim there is "20" so I need to query all nrtelefone that is not between 32 and 55 and not between 10 and 20, but the problem is with cursor this process will take to much time cause htere is too many clients..




Code Block
CREATE TABLE [LOGIN] (
[COD_CLIENTE] [varchar] (50),
[TRONCO_INI] [int] NULL ,
[TRONCO_FIM] [int] NULL ,
[TRONCOS_CLIENTE] [nvarchar] (4000)
)







Code Block
CREATE TABLE [PABX] (
[COD_CLIENTE] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TRONCO] [int] NULL CONSTRAINT [DF_PABX_TRONCO] DEFAULT (9999),
)





So is there a way to make this query without using cursors ? (NOTE: I'm using cursor cause I need to build the T-SQL at runtime just because of the TRONCOS_CLIENTE that the user will provide the SQL clause)

Thanks

View 4 Replies


ADVERTISEMENT

Query To Return Records Where One Table Is Void Of Linked Data In Another Table

Feb 13, 2008

I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.

Thanks in advance,

Mark

View 5 Replies View Related

Query To Move Certian Data From Sql Table To Access Table

Dec 14, 2007

hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.

thanks

View 5 Replies View Related

Update Query Containg Static Data And Data From Another Table.

Sep 28, 2006

Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.

View 4 Replies View Related

Change Data Table From System.data Into Sql Query

Apr 4, 2007

i someone had teach me how to write a query in datatable. however i need to get the data out from my database rather than the data table. can someone teach me how should i do it?esp at the first like.... like DataTable dt = GetFilledTable() since i already have set of data in my preset table i should be getting data from SqlDataSource1 right ( however i am writing this in my background code or within <script></script> so can anyone help me?   protected void lnkRadius_Click(object sender, EventArgs e)        {            DataTable dt = GetFilledTable();                    double radius = Convert.ToDouble(txtRadius.Text);            decimal checkX = (decimal)dt.Rows[0]["Latitude"];            decimal checkY = (decimal)dt.Rows[0]["Longitude"];                    // expect dt[0] to pass - as this is our check point            // We use for rather than fopreach because the later does not allow DELETE during loop execution            for(int index=0; index < dt.Rows.Count; index++)            {                DataRow dr = dt.Rows[index];                        decimal testX = (decimal)dr["Latitude"];                decimal testY = (decimal)dr["Longitude"];                        double testXzeroed = Convert.ToDouble(testX -= checkX);                double testYzeroed = Convert.ToDouble(testY -= checkY);                        double distance = Math.Sqrt((testXzeroed * testXzeroed) + (testYzeroed * testYzeroed));                        // mark for delete (not allowed in a foreach - so we use "for")                if (distance > radius)                    dr.Delete();            }                    // accept deletes            dt.AcceptChanges();                    GridView1.DataSource = dt.DefaultView;            GridView1.DataBind();        }

View 2 Replies View Related

Query Table Without Data

Mar 13, 2006

I'm writting a stored proc that has to query 2 tables. One table is a table of "jobs" and the other table contains jobs that have been invoiced (2 tables are jobs and invoicedJobs). The invoiced table only contains records for jobs that have an invoice and not jobs that do not have an invoice.
My dilemma is that I need to write a query that can retrieve all un-invoiced jobs in my stored proc. You can't rightly join a table that does not have a relationship with another table (can you?). So in my query for jobs with an invoice, I simply join my jobs table and invoice table based on a job id that both tables contain. But how could I perform a query for jobs that do not exist in my invoice table inside my stored proc? Any help would be greatly appreciated.

View 2 Replies View Related

Query To Get Data From Same Table

Nov 19, 2013

I have table with 4 columns Environmnet, LocationName, SettingName, Settingvalue

Table looks like

Environmnet, LocationName, SettingName, Value

values are
Prod, All, abc, abcdefrr
Test, All, gfd, rerwe
All, Newyork, fgsdf, dfgs
Demo, DC, erger, stgt
All, virgenia, fgaer, sdfasd
production, Virgenia, dfgd, drtert

I want query that displays for the condition where environment = all or production and locationname =all or virgenia

Environmnet, LocationName, SettingName, Value,

Production, All, abc, abcdefrr
All, virgenia, fgaer, sdfasd
production, Virgenia, dfgd, drtert

I want to display data in above result .

View 6 Replies View Related

Query On Data In Another Table

Mar 14, 2008

query on data in another table.
I have two tables.
Table 1
EventIDEventName
01234Jackpot


Table 2
EventNameAmount
Jackpot 123.00
EmptyCanister14.00

I would like to query Table2 based on the events in
the Table1. How do I do this ?
Not using select * from Table2 where EventName = 'Jackpot'

View 4 Replies View Related

SQL Query - Insert Data From One Table Into The Other

Oct 12, 2007

OK, this one should be easy for you folks but my mind is drawing a blank.
Can someone give me an example query to copy data from one table to another? Both tables already exist and both tables have the same table definition. tblA has data tblB is completely empty. I just want to copy from tblA into tblB.

I dont want to use the GUI, nor do I want to do a "Select * Into..."

Thanks

View 1 Replies View Related

SQL Query To Insert Data To A Table

May 21, 2007

Hi all...Can anyone give clue in doing below?Let say i have a table A with the following 3 fields. 1)Date2)Title 3)Status.i need to insert into table B (also have the same 3 fields) from thistable A with the condition where Title is "Alarm" and Status is "ON".This can be done by a simple "INSERT" query.However, there's a case for table A in like below:Date Title Status------ ------ ----------5/7/07 1:05:23am ALARM ON5/7/07 1:05:24am ALARM ON5/7/07 1:05:25am ALARM ACK5/7/07 1:05:25am ALARM ONBased on the table A above, i only need to insert from table A intotable B the first 2 ALARM(ON)s. The third ALARM(ON) which has the sameDate with ALARM(ACK) needs not to be inserted into table B.How can i write a simple SQL query which can insert all ALARM(ON)swhich doesnt have same date with ALARM(ACK)?Thanks. Pls help.Albert

View 1 Replies View Related

Query Data Type Of Column In DB Table

Aug 14, 2006

Hi All
I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion?
I use asp.net + msde
 
Thank you.

View 6 Replies View Related

Query To Filter Duplicate Data From Table.

Apr 8, 2004

i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

View 2 Replies View Related

Rearraigning Table Data In Query Output.

Apr 18, 2008

I am creating queries and reports in an Access front end (let the groans begin)...it is what they gave me to work with!

This particular query is pulling information from several different tables and manipulating it the way I want. However, there is one table that I am trying to pull info from that has the data arranged differently than all the others.

e.g.:

Name Drive Capacity Free_Space
Computer A C 40 10
Computer A D 200 175
Computer B C 80 55
Computer B D 500 445
Computer B E 500 365

I want that to look like:

Computer A C 40 10 D 200 10
Computer B C 80 55 D 500 445 E 500 365

I would prefer to do this within the existing query, without creating a new (permanent) table because the info needs to be real-time.

Any ideas/tips/leads?

View 7 Replies View Related

Query For Find Data Last Inserted In A Table

Apr 9, 2015

I need to find the history in SQl server.

I need to find the data, When was data last inserted/updated/deleted in a specific table?. is it possible?

Table name: Employee

View 1 Replies View Related

Editing Table Data In Query Analyzer

Jul 23, 2005

Hi All !I open a table in Query Analyzer by right clicking on it andselecting 'Open' and when the data is displayed in the view pane Iwould like to be able to edit it. It seems however that tables markedas read only (as shown on the top bar e.g. SQL Query Analyzer - [OpenTable ServerName.DBName.dbo.TableName(read-only)] ) can not be editedwhereas the one that do not have that attribute infront of the name Ican edit. Is there a way I can change the read only attribute of atable so that I can edit data directly in the view pane?Please let me know if there is !Thanks a heap ! :)Harkirat

View 2 Replies View Related

Query To Import Data From CSV To Azure Table

Sep 23, 2015

T-Sql query to import data from CSV to sql Azure table.

View 8 Replies View Related

Open Table With Query To Modify Data

Jul 23, 2007

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.



In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?



I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.



Please help, it seemed so much easier in 2000!

View 7 Replies View Related

Convert XML Data With Select Query And Insert Into Table?

Jun 19, 2014

I want to convert xml data with select query and insert into table.

Xml sample like this :

<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>

[code]....

And data should be like this with select query :

NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL

I want simple query in form of above format.

View 8 Replies View Related

Query To Compare Table Data Between Test And Production?

Jul 23, 2005

I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com

View 2 Replies View Related

Query To Get Linked List Kind Of Data From The Table

Sep 18, 2006

hi Experts,



I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of









ID
Duplicate_of
State

77637
65702
Duplicate

65702
42217
Duplicate

42217
-
Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.



Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.



/soni

View 1 Replies View Related

Query To Get Linked List Kind Of Data From The Table

Sep 18, 2006

hi Experts,



I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of









ID
Duplicate_of
State

77637
65702
Duplicate

65702
42217
Duplicate

42217
-
Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.



Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.



/soni

View 5 Replies View Related

T-SQL (SS2K8) :: DB Mail Query From Mentioned Data Format Table

Apr 28, 2014

I'm sending report based on below DB Mail query from mentioned data format table. But i'm getting invalid format output in csv file.

Table Data:

CREATE TABLE Sales_Data
(
ITEM_CODE varchar(25),
Price float,
Purchased_Date Datetime

[code]...

View 5 Replies View Related

SQL 2000: Query Question - Input Data Within Two Tables And 'between' Table

May 1, 2008

Hello specialists.

Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.


Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E

How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.

The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo

Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison

Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C

4 Group_E

Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4


I hope you can help me cracking this nut.

Thx in advance. Greetings Fred

View 3 Replies View Related

Transact SQL :: Query To Update A Table With More Than 150 Million Rows Of Data?

Sep 17, 2015

I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:

Source Tables :

OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,

[Code] ....

The update requirement is as follows:

DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
    SET rowcount 10000
    update r
    set Comp=t.Comp

[Code] ....

The update took more than 48h and didn't terminate , how to accelerate it ?

View 6 Replies View Related

SQL Query Question : How To Add Data To Two Tables Connected By Third Junction-table.

May 1, 2008


Hello specialists.

Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.


Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E

How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.

The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo

Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison

Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C

4 Group_E

Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4


I hope you can help me cracking this nut.

Thx in advance. Greetings Fred

View 7 Replies View Related

SQL Server 2012 :: Query Pulling Latest Info Data From Table

Aug 29, 2014

Let's say I have a table of data as per the below..

I'm trying to extract only the green highlighted items..

The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.

As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).

I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.

View 7 Replies View Related

Transact SQL :: Query Based On Date - Get Data From Table Above WHERE DateofBirth Is Tomorrow

Oct 30, 2015

Lets say I have a table, tblPersons

FirstName |  LastName   |  DateofBirth
----------------------------------------
  Thomas   | Alva Edison |   10-10-2015
  Benjamin | Franklin    |     10-10-2015
  Thomas   | More        |     11-10-2015
  Thomas   | Jefferson   |    12-10-2015

Suppose today's date is 09-10-2015 in (dd-MM-yyyy format), I want to perform a query in such a way that I should get the data from the table above WHERE DateofBirth is tomorrow, so I could get the following result.

FirstName |  LastName   |  DateofBirth
----------------------------------------
  Thomas   | Alva Edison |   10-10-2015
  Benjamin | Franklin    |     10-10-2015

View 15 Replies View Related

Loading Data From Same Table (oracle) In To Diff SQL Tables With In Same Package(same Query)

Nov 14, 2007



Hi,

I have Table A . we already have 80 columns . we have to add 65 more columns.

we are populating this table from oracle .and we need to populate those 65 columns again from the same table.

Is it a better idea to add those new 65 columns to the same table or new table.

If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.

I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?

View 1 Replies View Related

T-SQL (SS2K8) :: Pivot Query - Convert Data From Original Table To Reporting View

Apr 8, 2014

I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.

Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14

View For Reporting:

Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId

Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14

View 6 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data  in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

Query Table Data From Server &#34;A&#34; By Server &#34;B&#34;

Mar 23, 2000

I need to query data in a table located on Server "A" from Server "B".
I do not want to use replication. The only way I know of to do this is
have a process on server "A" run which creates a output text file out on the network. Then have a process on server "B" pick up that text file and BCP it into a table on Server "B".

Isn't there a cleaner way of doing this?

View 7 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved