How To Extracting Data Into Three Table

Mar 22, 2007

This is my sample table Data

table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............

TagID--Primary Key

table2

TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key

table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................

DetID---Primary key
TagID and TopicID----Foreign key


i need Topic based on TagName.


This xyz(TagName) have a 3 Topic. i need the 3 Topic

Once i select 123abc(TagName) that particular Topic I need..

any Query in Single Line.......................

View 2 Replies


ADVERTISEMENT

TSQL Statement Extracting Data From One Table Through Another Table

Nov 17, 2007

Hi,

I have 2 tables,
MembersTemp and Organisations

I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.

Initiallt I only have the ExecID for the MembersTemp table

MembersType table:
ExecID 3013
OrganisationID 4550

Organisation table:
ID 4550 (PK)
Name "Microboff"

Any ideas??

View 5 Replies View Related

Extracting Data From A Table For Archiving Purposes

Sep 22, 1999

Hello,

I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.

My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.

Sorry this is so long...

Thanks,

Brian R
WESNet Systems, NOC

View 1 Replies View Related

Extracting A Date From A Table

Oct 13, 2007

Hi, im currently learning sql. I have a problem extracting a date from a table.

SELECT Date FROM Flight

Gives me a "syntax error in sql expression"

in the table the date coloum - fields are set Date/Time

an example field is 28.12.2007 00:00

Thanks in advanced!

Steve

p.s. im using openoffice base, if i force to run the code it display's the date. however I would like it to run without errors. Thanks again

View 4 Replies View Related

Extracting Information Outside A Table

Feb 22, 2008

Hi,

I have two tables - a stock table and a transaction table. The stock table is called stock and the transaction table is called trans. The table make a point of sale database. Both table have a joining field called prod_no.

I want to extract the stock line by querying stock.prod_no that are not present in the trans table for a period of time specified.

Basically, I want to find dead stock lines that have not been sold and, therefore, are not in the transaction table but that have a stock figure. I have to enter in a start and end date paramater using the ddate field in the trans table. I have looked at left, right and outer joins but with no luck and don't even know if this is the correct query type to use.

any help would be much appreciated,

View 4 Replies View Related

Extracting Result Which Are Not There In The Table

Dec 4, 2007

I want to see all the product line provided they are there in the table or not,
i.e.








Product Line
Product Line Description
Total Usage Quantity

10000
Raw Material
0

20000
Intermediate Product
0

30000
Bearing
2713321

32000
High Strength
4258197

34000
High Temp
0

36000
Corrosion Resistant
639492

50000
High Speed
1452153

52000
Die Steel Hot Work
1614727

54000
Die Steel Cold Work
464943

88000
Misc
0

90000
Conversion
0
This is the result i am looking out for now the problem is that those record are not there in table so naturally they will not be shown in the result.
Can any one help me modifying below query? so that i can get the aforesaid result



Code Block

SELECT [PRODL] as 'Product Line'
, (case when prodl = 10000
then 'Raw Material'
when prodl = 20000
then 'Intermediate Product'
when prodl = 30000
then 'Bearing'
when prodl = 32000
then 'High Strength'
when prodl = 34000
then 'High Temp'
when prodl = 36000
then 'Corrosion Resistant'
when prodl = 50000
then 'High Speed'
when prodl = 52000
then 'Die Steel Hot Work'
when prodl = 54000
then 'Die Steel Cold Work'
when prodl = 88000
then 'Misc'
when prodl = 90000
then 'Conversion'
end)
as 'Product Line Description'

,sum(case
when [PRODL] = 10000
then [Usage Qty]
when [PRODL] = 20000
then [Usage Qty]
when [PRODL] = 30000
then [Usage Qty]
when [PRODL] = 32000
then [Usage Qty]
when [PRODL] = 34000
then [Usage Qty]
when [PRODL] = 36000
then [Usage Qty]
when [PRODL] = 50000
then [Usage Qty]
when [PRODL] = 52000
then [Usage Qty]
when [PRODL] = 54000
then [Usage Qty]
when [PRODL] = 88000
then [Usage Qty]
when [PRODL] = 90000
then [Usage Qty]
end)
as 'Total Usage Quantity'


FROM [LATCUBDAT].[dbo].[RMU]
group by prodl
order by prodl


Result of The Aforesaid Query








Product Line
Product Line Description
Total Usage Quantity

30000
Bearing
2713321

32000
High Strength
4258197

36000
Corrosion Resistant
639492

50000
High Speed
1452153

52000
Die Steel Hot Work
1614727

54000
Die Steel Cold Work
464943

View 5 Replies View Related

Extracting Data From A Device

Feb 6, 2001

hi all,

i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?

thanx.

:-)

hemant

View 2 Replies View Related

Extracting A Portion Of PK Data

Mar 27, 2008

I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.

View 1 Replies View Related

Extracting Data From 2 Tables.....

May 19, 2004

Cheers

I would really apprecite if anyone could make a comment on this...

what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....

* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'

Thanks

View 5 Replies View Related

Extracting Data From SQL CE SDF File

Dec 9, 2005

Hi,

View 8 Replies View Related

Extracting Data From Sql Server 6.5

Jan 22, 2008

Hi!

I'm analyzing the problem of extracting data from sql server 6.5 in order to load it into oracle.

I already know about the bcp tool, but I'd like to know if you would consider any other approach based on SSIS, or any other tool like SQL Load.

regards, and thanks in advance for your help.

View 1 Replies View Related

Extracting Data From Csv With A Different Name Everyday

Jan 30, 2008

I am new to SSIS and everything in it. I have been reading up on it but I am stumped. I need to do the following:
Automatically open a .csv file that will have a different name each day
Extract the data
Convert it to SQL format
Append it to it to an existing SQL table in my DB
Delete or move the .csv file to a different directory

Thanks for the guidance!

Matt

View 4 Replies View Related

Extracting Data From A Variable

Jul 30, 2007

hi All,
I have a string varaible passed to the SP something like :
@var = 'v1#1@v2#1,2@v3#1,3,4,5'. Now i have to extract the data from this variable in such a way that :
select * from var_data shud return like this :
ID Role
v1 1
v2 1
v2 2
v3 1
v3 3
v3 4
v3 5

Plz guide me how to achieve this result from the variable.
Thanks in advance :-)

View 1 Replies View Related

Extracting Sql Table Column Names

Feb 5, 2006

I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query



SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')

View 4 Replies View Related

Lookup Table, Extracting Values

Sep 21, 2007

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?

View 10 Replies View Related

Extracting Data From A Varchar Variable?

May 19, 2004

Guys.

I have some data like this one:

'UploadfilesCompanyIDProjectCodeFilesFile1.doc'

P.S: the values may vary each time.

I need to programmatically extract from that kind of string only file1.doc

Ho can I dynamically do it? any function? statement? etc? please help.

The idea is getting only the string after the last ""

Thanks
Fr

View 1 Replies View Related

Sql Query Problem While Extracting Data

Oct 18, 2005

heyI have a problem string index;Command.CommandText = "Select * from employees where LastName = ' " Index " ' ";but the problem is iam not able to pass the index  value properly like it should be ( ' Index ' ) for SqlServer2000.but its either giving me an error or its not taking the value properly.Can anyone help me out with that.Thanks 

View 3 Replies View Related

Extracting Data From An Email Or Website

Feb 1, 2002

Can someone point me in the right direction. I would like to grab some data from an email (not as an attachment)and load into a table, but I have no idea where to begin. Also, can this data also be retreived from a website?

Thanks
BV

View 1 Replies View Related

Extracting Data From Exchange Into SQL Server Db

Mar 22, 2002

Has anyone been able to solve the issue with setting up a package in EM, which connects to an Exchange(.edb) database and extracts the data into a SQL Server db. Previous message posts don't offer much insight. So far, all I have is using Access 2000, to connect & move the data, then extract from here.
Thanks in advance for any help.
BV

View 1 Replies View Related

Extracting Data From MSSQL To MSExcel

Jul 13, 2004

I recall something about Analysis Services (SQL server tool) that is able to extract data from the DB tables (select multiple tables if one wishes (and to link those tables up ??!?)) then extracted to MS Excel. It has been a while since the course was last taught, I'm not at all sure how the steps were, or how Excel were able to display them. Would someone advice or refer to a tutorial if possible?

Much thanks!

View 1 Replies View Related

Extracting From A DATETIME Data Type

Mar 16, 2004

Hi,

I can't figure out how to extract just the date or just the time from a DATETIME data type? Any suggestions?

View 2 Replies View Related

Extracting Data Based On Two Fields

May 27, 2014

I have a table having multiple fields but I need to extract data based on the two fields.

Field1 Field2
1 | 123
1 | 213
2 | 123
2 | 3654
2 | 4758

I want the result with 2 cases

1.) distinct in Field 1 but multiple in field 2

Expected result:

Field1 column1 column2 column3
1 | 123 | 213
2 | 123 | 3654 | 4758

2.) multiple in field 1 but distinct in field 2

Expected Result :

Field 2 | Column1 | Column2
123 | 1 | 2

There may be more columns added according to the data available.

View 7 Replies View Related

Extracting Data To The Left Of Char(10)

Oct 21, 2014

I have a description that is dynamically built for a part, with variables for this part split by line feeds. For example

100" widget
Color: White
Holes: Many
Material: Wood

This makes everything nice an neat on reports, etc, and the world is good. There are a lot of better ways to do this I am sure, but due to the limitations of the software we are using, this fits the bill best.

In SQL view, it LOOKS like the lines are all run together, but when you actually pull the data, it formats properly.

Enter a new need to identify just the first "line" of info. I would like to be able to query all characters left of the CHAR(10). In the example above, the results would be just 100" Widget. I have tried a couple ways using Left(field,charindex(char(10))-1 but nothing seems to get me data back. Is this even possible within a View?

View 1 Replies View Related

Extracting Data From An Excel File

Dec 4, 2007

I have an Excel 97 file that has a lot of data in it and I need to pull out pieces of that data. Here is one example of the data I have. It is in colum C in Excel.

Row Column C
1 11/25/07
2 12/01/07
3 week 1
4
5
6 9,708
7 128,353
8 -
9
10 42,691
11 3,000
12 183,751
13 $183,751


What I want to get out of this to put into a SQL table in one record is (I've included a header row here):

StartDate EndDate Week Value
11/22/07 12/1/07 1 183751

What is the best way to attach this? I have several other items I need to pull out of this Excel File, but if I can get this figured out I can adapt it to get the rest of what I want.

View 4 Replies View Related

Problem Extracting Data From Oracle

Feb 15, 2008



hello



hope someone can help with this problem. I've created a package which copies data from an oracle 10g database into excel. the package works fine when executed in BIDS, at the command line or using dtexec but fails whenever i try and schedule it as a sql agent job. The package was created as the same user that sql agent runs as. I'm getting the following error message....



Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2008-02-15 14:28:03.85 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OPRD.epsenq" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. The package execution fa... The step failed.



does oracle client tools need to be installed in a certain locaion? the server is 32 bit.



thanks
iain


View 1 Replies View Related

Extracting Data From LotusNotes Into SQL SERVER

Feb 15, 2007

I have a form in Notes that is huge. I need to extract a part/field from that form into SQL SERVER. If I use NotesSQL Thru SQL DTSPackages, I am afraid it will run very long. Is there any other option on that?

Any help is appreciated.

View 2 Replies View Related

Extracting Olap Data Into SQL Using SSIS

Apr 2, 2008

I am using data flow task.And data flow source uses ole db for olap 9.0 to connect my ssas. sql comment is my access mode. A mdx query extracts data. Data flow destination is sql server table. Error said Data Flow Task: OLE DB Source [579]: The output "OLE DB Source Output" (589) references an external data type that cannot be mapped to a Data Flow task data type. I guess it is a implicit data type convertion problem. But how to solve it???

I appreciate for all suggestion. Thanks.

View 6 Replies View Related

How Can I Speed Up Extracting Data From Oracle?

Apr 27, 2007

Hello,



I'm trying to pull 1.2 million rows down from an Oracle database into SS2005. It has been painfully slow (about 20 - 30 minutes). Is there a way that I could speed this up?



While I am connecting to Oracle 9, I tried downloading the latest drivers (10g) from Oracle because I thought I could use the ODP.NET drivers. Unfortunately it did a number on my system and I could no longer even ping one of the databases.



I've seen this question sort of answered, but the only answer that I've found seems to be to use a custom connector developed by http://www.persistentsys.com. That is great that they have developed something, but I would expect that I should be able to use Oracle and/or Microsoft's drivers to get similair performance. (OK, I just don't want to pay for it )



I was thinking I could do a bulk dump from Oracle, into a flat file and then bulk load it into SQL Server, but I don't have (or know about) tools that can bulk dump from Oracle.



Thank you for the help.



-Gumbatman

View 7 Replies View Related

Extracting Specific Values From One Table And Insert Them Into Another

Jun 3, 2008

Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
Could anyone help me construct this query?

View 2 Replies View Related

SQL 2012 :: Extracting Node From A Column In A Table That Contains XML

Dec 4, 2014

I am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause.

What I have so far is:

SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth')
FROM tblCCH AS CCH

View 3 Replies View Related

Extracting And Joining Header From Denormalized Table

Dec 1, 2005

Hello,I am currently working on a monthly load process with a datamart. Ioriginally designed the tables in a normalized fashion with the ideathat I would denormalize as needed once I got an idea of what theperformance was like. There were some performance problems, so thedecision was made to denormalize. Now the users are happy with thequery performance, but loading the tables is much more difficult.Specifically...There were two main tables, a header table and a line item table. Thesehave been combined into one table. For my loads I still receive them asseparate files though. The problem is that I might receive a line itemfor a header that began two months ago. When this happens I don't get aheader record in the current month's file - I just get the record inthe line items file. So now I have to join the header and line itemtables in my staging database to get the denormalized rows, but I alsomay have to get header information from my main denormalized table(~150 million rows). For simplicity I will only include the primarykeys and one other column to represent the rest of the row below. Thetables are actually very wide.Staging database:CREATE TABLE dbo.Claims (CLM_ID BIGINT NOT NULL,CLM_DATA VARCHAR(100) NULL )CREATE TABLE dbo.Claim_Lines (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_LN_DATA VARCHAR(100) NULL )Target database:CREATE TABLE dbo.Target (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_DATA VARCHAR(100) NULL,CLM_LN_DATA VARCHAR(100) NULL )I can either pull back all of the necessary header rows from the targettable to the claims table and then do one insert using a join betweenclaims and claim lines into the target table OR I can do one insertwith a join between claims and claim lines and then a second insertwith a join between claim lines and target for those lines that weren'talready added.Some things that I've tried:INSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLGROUP BY T.CLM_ID, T.CLM_DATAINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Target.dbo.Target TINNER JOIN (SELECT CL.CLM_IDFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID =CL.CLM_IDWHERE C.CLM_ID IS NULL) SQ ON SQ.CLM_ID = T.CLM_IDI've also used EXISTS and IN in various queries. No matter which methodI use, the query plans tend to want to do a clustered index scan on thetarget table (actually a partitioned view partitioned by year). Thenumber of headers that were in the target but not the header file thismonth was about 42K out of 1M.So.... any other ideas on how I can set up a query to get the distinctheaders from the denormalized table? Right now I'm considering usingworktables if I can't figure anything else out, but I don't know ifthat will really help me much either.I'm not looking for a definitive answer here, just some ideas that Ican try.Thanks,-Tom.

View 2 Replies View Related

Extracting The Specified Record No Of Records From Database Table

Jun 11, 2006



hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

View 5 Replies View Related

Extracting XML From An SLQ Table Column Then Getting Delimted Vales From Within That XML...

Dec 6, 2007

Hi all.

This is my first SSIS project it's also an utter mare.

I'm a C# developer with reasonable SQL knowledge but I haven't used SSIS projects in BIS before.

What we have (for some insane reason) is an SQL table like so:

Column A |Column B |Column X |

Where Column X contains XML like this:
<Column C></Column C><Column D></Column D><Column ArrgWTF></Column ArrgWTF>

Where <Column ArrgWTF> contains two '-' delimited values.

What I need is some guidence (or just being told if its possible or not) to take this data and arange it like so (see bellow) in a new SQL table:

Column A |Column B |Column C | Column D |Column ArrgWTF1 |Column ArrgWTF2 |

Where A&D are from the original SQL table, C & D are pulled from the XML and ArrgWTF1 & 2 are sourced from the '-' delimited vales within the XML.

Many, many, thanks in advance.

View 6 Replies View Related







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