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
ADVERTISEMENT
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
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
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
View Related
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
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
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
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
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
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
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
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
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
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
Jan 15, 2007
Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick
View 6 Replies
View Related
Nov 20, 2015
I want to calculate the target based on Flag value if Flag value is "Y" ....than MAX(Customer Target) else MAX(SLA target).Flag column contains "Y" , "N" and some blank values . Flag, Customer Target and SLA target are the columns in Table1. I have used the below formulas
Target:=IF('Table1'[ Flag]= "Y",MAX('Table1'[Customer Target]),MAX('Table1'[SLA Target]))
Target:=IFERROR(IF('Table1'[Flag]= "Y",MAX('Table1'[Customer Target]),MAX('KPI'[SLA Target])),BLANK())
View 7 Replies
View Related
Jun 29, 2012
I wanted to know that when I create a table using SQL Server, does the table metadata like schemaname and catalogname is stored in memory or in hard-disc. I am trying to extract schemaname and catalogname when the database is down or correctly when I have no connection string. I know about sysindexes, systables etc, but i am not able to understand whether it stores the data which is useful to me like catalogname.
sql query to extract the column names from sysindexes?
View 2 Replies
View Related
Jul 20, 2015
I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.
Here is the scenario:
I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).
I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).
Is this possible using DAX ?
View 6 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Mar 17, 2008
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
View 3 Replies
View Related
Jun 6, 2006
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...
Or using a table-valued function to return a temp table as the result.
I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
View 3 Replies
View Related
Nov 29, 2004
I got one table with 3 columns = Column1, Column2, Column3
Sample Table
Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0
Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0
Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0
The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner
Column1 | Column2 | New1 | New2|
Thanks
View 3 Replies
View Related
Jan 3, 2006
Hi all
I have a problem to which I'm not finding a solution for, and I thought maybe some of you could give me a few hints on how to approach it.
I need to make available a SP.
For each row returned by the SP, I need to make an insert on a 2nd table, using one of the parameters used on the SP, the PK of the original table and some more info. As in to identify rows, with a specific execution of a particular SP.
Any ideias?
Thanks in advance,
Jorge Carvalho
View 2 Replies
View Related
May 21, 2008
Hey guys I'm trying figure out if there is a way to insert a result from a stored procedure into a table without going through ASP.NET. This is just an experiment and for learning purposes since i did manage to work it in .NET.
For example.
CREATE PROCEDURE dbo.inserttoLog
@Modem_ID VARCHAR(7)
As
Select dbo.PRODUCT.MODEM_ID + ' ' + dbo.PRODUCT.COLOUR + dbo.PRODUCT.SPEED AS Part#
FROM PRODUCT
WHERE ([Part#] LIKE '%' + @Modem_ID + '%')
I want to take the concatenated result of PART# and insert it into a log table. Is there a way of doing that?
View 12 Replies
View Related
Jul 20, 2005
im not very good at sql but need to query the database to use in myprogramming script.if the database is just like thisid name parent_id1 A null <----- root2 B 13 C 14 D 15 E 26 F 27 G 38 H 49 I 410 J 411 K 9the data in the above table is just like some sort of tree datawhich have parent and child nodeand now,how to query into the result like thisthe deepest node is in level 4what i want is how deep this tree data is?anyone show me the query script or store procedure to find theresult please?many thanks in advance,--Posted via http://dbforums.com
View 1 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Dec 27, 2005
question: get 10 gram of the gold from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error;
here my code:
--------test condition-------------Create table Qt(id int identity primary key,Q int)Goinsert into Qt(Q) values(8);insert into Qt(Q) values(6);insert into Qt(Q) values(6);insert into Qt(Q) values(5);insert into Qt(Q) values(3);insert into Qt(Q) values(3);insert into Qt(Q) values(1);insert into Qt(Q) values(0);Go-------------------------------
Create procedure Test2( @m int) ASdeclare @i int,@j int,@n int ,@id int ,@q int ,@last intset @i=1;set @j=1;Create table #result(id int, Q int)declare __cursor cursor forselect * from Qt where Qt.Q <=@m order by Qt.q desc for read onlyOpen _cursor;select @n=@@cursor_rows;fetch last from _cursor into @lastif @n > 0 -------------------beginBegin_this:fetch absolute @i from __cursor into @id,@qif @q=@m begininsert into #result(id,Q) values(@id,@q);GoTo End_this;End-------------------else-------------------------------begin------------------------if @q=@lastbeginSet @j=@j+1;------------if @j>@n Goto End_this;elseBeginset @i=@j;Delete from #result;End------------Endelsebegininsert into #result(id,Q) values(@id,@q);set @i=@i+1;end------------------------GoTo Begin_thisEnd-----------------------------
End_this:select id,Q from #result--close __cursor--deallocate __cursor
.....please help me, =A='
View 6 Replies
View Related
Apr 13, 2001
I used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?
View 1 Replies
View Related
Sep 3, 2004
hai..
is it possible to store the result the following statement in a table
execute master..xp_cmdshell 'dir'
when i execute this in QA i get the result. but i want to store it in a table
thnks in advance
with regards
Sudar
View 6 Replies
View Related
Jun 13, 2008
One column in my table stores SQL queries(QueryCoulmn). Another coulmn supposed to store the result of those queries(ResultColumn). Can I run an update query or how can I do that? I could not figure out the syntax.
update tablename
set ResultColumn=exec(QueryCoulmn)
thanks
View 6 Replies
View Related
Jun 21, 2014
I have the select statement below - where I use some external functions - and I would like the result to be saved to another table.After executing the statement I end up with a table containing the following columns:
accountno
d.date_start
d.date_end
TWRR_Month
TWRR_Cum
Normally I would use the command 'INTO TableName' but since the statement is so long I don´t know where to place it.The select statement is as follows:
IF OBJECT_ID('tempdb..#trn') IS NOT NULL
DROP TABLE #trn
IF OBJECT_ID('tempdb..#mv') IS NOT NULL
DROP TABLE #mv
SELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn,
CASE PTR_TAC
WHEN 'BUY' THEN 0
ELSE PTR_LOCALAMT
END as amt_trn
[code]...
View 2 Replies
View Related
Nov 7, 2014
I am comparing names of people from a table without id field to a table that has those people along with their ids.
I have a select statements using different field combinations that fetches ids of these people and presents the result set like:
table1id,table1firstname,table2firstname,table1lastname,table2lastname
E.g.:
1 john john smith smith
1 john j smith smith
1 john jon smith smit
I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...
View 1 Replies
View Related
Jul 23, 2005
Hello group!i have a table "group_code" wich relates the names ofnt-(domain)-groups to codes. now i want use the stored procedurexp_logininfo (asking for the group-membership of the current user) tojoin the result to "group_code". then i must use the new result (thecode) to join against other tables.i know now, that i cant join results of SPs against tables. may be thata UDF with a table result is the correct approach. but i have no ideahow to wrap the xp_logininfo in a UDF.a other way can be to do in a UDF the same thing like the xp_logininfo.then this UDF should deliver at least the nt-(domain)-groups in wichthe current-user is a member.Is there anybody who can give me the code for that?many thanx in advance.Karl
View 1 Replies
View Related
