Saving Select Result To A Table
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
ADVERTISEMENT
Jan 3, 2001
Hi all,
I want to save DBCC's result into a table to execute my statement but I don't know how to, exmple for DBCC OPENTRAN (my database) - how can I put SPID & UID to my variable from this statement?
Thanks in advance
Happy New Year
PTH
View 1 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
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
Sep 4, 2006
Hi Gurus,
Can u help me out in the following two scenario/problems.
1. I want to save the query results (which is generated within execute sql task) to file (which does not exists & should be created at run time). is it possible?
2. Can I define the file name, in Flat File connection Manager, dynamically (by using some variable)?
Your support and help will be appreciated...
regards,
Anas
View 8 Replies
View Related
Sep 14, 2014
Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -
Result should be -
ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two
Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three
select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing
View 2 Replies
View Related
Dec 21, 2006
Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null
declare @1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,
@9 int,@10 int,@11 int,@12 int,@21 int,@22 int,@23 int,@24 int,@25 int,
@26 int,@27 int,@28 int,@29 int,@30 int,@31 int,@32 int
set @21=(select count(*) from j9a whereIngestion=1)
set @22=(select count(*) from j9a whereInhalation/nasal=1)
set @23=(select count(*) from j9a whereAspiration=1)
set @24=(select count(*) from j9a whereOcular=1)
set @25=(select count(*) from j9a whereDermal=1)
set @26=(select count(*) from j9a whereBite=1)
set @27=(select count(*) from j9a whereParenteral=1)
set @28=(select count(*) from j9a whereOtic=1)
set @29=(select count(*) from j9a whereRectal=1)
set @30=(select count(*) from j9a whereVaginal=1)
set @31=(select count(*) from j9a whereOther=1)
set @32=(select count(*) from j9a whereUnknown=1)
Create table table9(Route varchar(30),Fatal int)
insert into table9 values ('Route_Ingestion',@1,@21)
insert into table9 values ('Route_Inhalation',@2,@22)
insert into table9 values ('Route_Aspiration',@3,@23)
insert into table9 values ('Route_Ocular',@4,@24)
insert into table9 values ('Route_Dermal',@5,@25)
insert into table9 values ('Route_Bite',@6,@26)
insert into table9 values ('Route_Parenteral',@7,@27)
insert into table9 values ('Route_Otic',@8,@28)
insert into table9 values ('Route_Rectal',@9,@29)
insert into table9 values ('Route_Vaginal',@10,@30)
insert into table9 values ('Route_Other',@11,@31)
insert into table9 values ('Route_Unknown',@12,@32)
select * from table9
The exRoute result is like this
70 Ingestion
71 Inhalation
72 Aspiration
73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal
The above giving the errors
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Inhalation'.
Thanks in advance
View 1 Replies
View Related
Jun 12, 2014
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
View 3 Replies
View Related
Jul 23, 2005
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
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
Jul 16, 2007
What is the syntax for saving a single value returned by a Select statement in a variable? I have tried the following, but its telling me I "Must declare the variable '@TempCount'." which is already declared!
SET @SQL = 'Select @TempCount = count(*) FROM ' + @TblName + ' WHERE ' + .......
exec(@SQL)
View 1 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
Jun 11, 2007
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
View 16 Replies
View Related
Dec 13, 2007
Hi Guys,
I am trying to automate a basic task using SQL Server 2005 Express.
Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.
All ideas gratefully received!
View 1 Replies
View Related
Jun 14, 2007
Hi
I’m making a very primitive shopping cart. I’ve created a table containing a list of products and using an SQLdataSource with a GridView I’ve got the products displayed – simple.
What I now need is to add a button with an Insert command, but the SQL needs to add a row of data to a different table – tblShoppingBasket. I’ve tried adding in the insert command and changing the SQL but it doesn’t add a new record, although is also doesn’t through and error.
The second part of the problem is need to pass the productID from the row they selected (pressed the button that triggered the insert command) and add this to the shoppingBasket table via the insert SQL.
I’d really be grateful for any help (code below).
ThanksRichard
<asp:SqlDataSource ID="SqlDataSource_GenralProducts" runat="server" ConnectionString="<%$ ConnectionStrings:AquilaConnectionString %>"
SelectCommand="SELECT * FROM [tblProducts] WHERE ([productAgeGroup] = @productAgeGroup)"
InsertCommand="INSERT INTO [tblShoppingBasket] ([userID], [productID]) VALUES (@userID, @productID)">
<SelectParameters>
<asp:Parameter DefaultValue="3" Name="productAgeGroup" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="userID" Type="Object" DefaultValue="123"/>
<asp:Parameter Name="productID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" DataKeyNames="productsID"
DataSourceID="SqlDataSource_GenralProducts">
<Columns>
<asp:ImageField DataImageUrlField="productThumbNail" DataImageUrlFormatString="../images/shop/thumbNails/{0}">
</asp:ImageField>
<asp:BoundField DataField="productLabel" HeaderText="Product:" SortExpression="productLabel" />
<asp:BoundField DataField="productDescription" HeaderText="Description:" SortExpression="productDescription" />
<asp:BoundField DataField="productCost" HeaderText="Cost:" SortExpression="productCost" />
<asp:ButtonField ButtonType="Button" CommandName="insert" Text="Add to Shopping Basket" />
</Columns>
</asp:GridView>
View 1 Replies
View Related
Jun 21, 2007
In the database i have a table with this information:key_id =1key_desc =43+34+22+12I want sum the values in key_desc. Something like:SELECT key_desc FROM tableBut the result of the select was "111" and not "43+34+22+12".Is this posible?
View 6 Replies
View Related
May 10, 2006
Hi there,
Problem:
Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.
Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:
SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1
Is there a way to realize this ?
Thx for helping
View 5 Replies
View Related
Jan 30, 2007
hey there
I have parameters in my report set up
CallStatus = string
Available Values are non-queried and values are listed in the value list
closed, open, pending, select all
If I choose each one separately I get a result - if I choose select all I get no result.
This worked fine attaching to another datasource - I have now attached it to a new source.
Tried altering the relationship to properties to a left outer join but this made no difference.
Can someone tell me what else I should check?
thanks
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
May 29, 2012
I have procedure. say for example...
begin
select * from table_abc
end;
suppose, i get errors i.e table not found etc etc. I want to save the errors in a file or even in a table will do.
View 1 Replies
View Related
Jan 22, 2008
I am not getting the correct schema once I've created a new table in SQL Server 2005. I am expecting to see dbo.Table_Name. Instead I'm getting UserName1.Table_Name. I was set up as a DB owner. How can I get what I'm expecting to see?
View 5 Replies
View Related
Jan 21, 2002
Hi,
I ran a sql somedays ago it gave me result, I ran the same sql today it gave different result. The only difference is that the particular table in the sql is frequently used table with lots of new insert. But the sql I ran is not selecting the new rows, it only deals with already inserted records.
Any idea what will make the sql to give different result from same set of records in different time.
Thanks
John Jayaseelan
View 3 Replies
View Related
Oct 17, 2006
Let's say I have a simple query to return the results of my "Status" table. The query reads as follows:
Code:
Select statusID, statusName
From Status
Here is the result set that I am returned:
Code:
22 Associate Member
23 Is Not Paying
24 Exempt
25 Fair Share
26 Member
29 Retiree
30 Staff
32 Fair Share - Self Pay
34 Member - Self Pay
Now, I am using this query for reporting purposes and would like to inject some additional sql that will append one additional row to my result set -- this is what I am calling the 'static' row in the thread title.
In other words, without modifying my database I would like to return the above set of data but with one additional row that has an arbitrary ID with the name "Unknown" or something similar.
again, I want to avoid adding an "Unknown" field directly to my database -- is their any way to "hard code" the selection of this row in my sequal?
Thanks,
Zoop
View 1 Replies
View Related
Mar 20, 2007
Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?
thank you
eliko"
View 1 Replies
View Related
Mar 14, 2008
Hi, I execute a select statement
SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'
every times I execute it I get different result.
1st result : 17036986
2nd result : 17037903
3rd result : 17038309
Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.
View 18 Replies
View Related
Jun 13, 2006
Can we select "last 10 rows" from the result set. (sql server 2000)
Something of an opposite of "top" functionality
View 3 Replies
View Related
Dec 6, 2005
Hi,Using VB.net I have created a custom object (hope my terminology is correct here), it’s just a class that defines a few string, integer, and hash table variables. After creating an instance of this class and populating it with data I need to be able to store this instance of my object in a sql server data base table. How do I do this? I saw an article that used the image data type to achieve this (code was in java unfortunately) is this the correct approach. Could you please give me some code examples
Thanks
View 1 Replies
View Related
Mar 10, 2000
Hello,
Yesterday I was looking to the processor usage in the Task Manager of Windows NT when a script of mine was running. The script was an InfoPump Script; which is a tool from the DecisionBase suite from CA (was previously owned by Platinum). This script contains SQL statements that select data from several tables and stores the result into another table. The SQL code used for this looks fine to me.
The query was running on a Compaq Proliant 5500 with 4 500 Mhz Xeon processors, 1 GB RAM, NT Server 4, SP 5, RAID 5. The SQL Server is configured to use all resources and SQL has normal priority on NT.
When the select part was running al four processors were used for about 75% and when the store happens only 1 processor is used for 100%.
Why is the store not spread over all four processors? It only uses one processor and it seems to be a bottleneck.
Stef
View 2 Replies
View Related
Feb 22, 2001
Does anyone know how to save the output of a stored procedure into a table? I would like to be able to use some procedures such as the sp_helpfile and dbcc sqlperf(logspace) and save to output into a table. Does anyone have any ideas?
View 2 Replies
View Related
Jun 11, 2008
Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
View 3 Replies
View Related
Jun 13, 2005
Hi AllI hope some1 with more experience of Sql Server 2000 Stored proc's can help me or point me to somewhere I can find the info cos at the moment it's doing my head in, I have spent 2 day,s so far trying to solve the following query, the problem is I do not understand the errors I am getting or why so here is the code that won't work
ALTER PROCEDURE dbo.UpdateStock
@OrderID intASSELECT (dbo.Products.Stock - dbo.OrderDetails.Quantity) AS NewStockFROM dbo.OrderDetails INNER JOIN dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductIDWHERE (dbo.OrderDetails.OrderID = @OrderID) UPDATE dbo.Products SET dbo.Products.Stock = NewStockWhat I am trying to do is get the OrderDetails.Quantity form the OrderDetails table the UPDATE the Stock feild in the Products Table by deducting the OrderDetails.Quantity From the Products.Stock to give me the new stock levelWell it works to an extent in that if I do this to SET dbo.Products.Stock = 10 it will put 10 in the Stock feild but I am unabke to get the value of NewStock , which caluculates the correct value into the set statment I keep getting invalid colum name, I have tried any combination I can think of but I still get error of 1 sort or another.So if any1 could help as I am pulling my hair out with this 1So perhaps some1 could cast there eye over it pleaseThanks
View 2 Replies
View Related
Jun 6, 2012
I want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?
The original data is from an MS SQL database to be transferred to a PostgreSQL database.
MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)
I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.
View 2 Replies
View Related
