How To Use Results Of Select As SP Input
Oct 17, 2012
I need to insert in TableB the results of a select over TableA so i used:
Insert into TableB (col1, col2, col3) select c1, c2, c3 from TableA
and works OK.
But now i need to use a Stored Procedure "Insert_in_TableB" that handles all the process of checking values, inserting data, logging steps, etc etc. That receives via its parameters the values for validate, etc and then insert them in TableB.
So, how can i execute that procedure (and define its parameters) in a easy way? Something like
Exec Insert_in_TableB (select c1, c2, c3 from TableA)
or
Select c1, c2,c3 into Insert_in_TableB...
Don't want to use cursors.
View 1 Replies
ADVERTISEMENT
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
May 14, 2007
Hi
I have a strange issue here.
Both the input datasets of the merge join are sorted . Everything works fine if the dataset doesn't contain speacial characters like - (hipen).
This is the sorted dataset.ASI311_3ASI311_3ASI312ASI311ASIKit1ASIKit1
Merge Joiner has to look for ASI311 and finds it in the list and everything works as expected. But if the dataset is changed to
ASI311_3ASI311_3ASI-312ASI311ASIKit1ASIKit1
Then it doesn't find the ASI311 in the above dataset ?
Only change between the two datasets is the - (hipen) which is added in one of the field. Does SSIS has a differenent precedence of the special characters than that of the Database. Both the datasets are on SQLServer.
Can anyone point out the issue here?
Thankyou
Vidya
View 4 Replies
View Related
Jan 11, 2008
Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO
-- OR
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.
Thanks in advance,
Scott Chang
View 5 Replies
View Related
Dec 22, 2002
Hi,
im getting from my first select a list of pairs of codes (let say the codes r of products.)
so i have something like:
FirstCode SecondCode
1 1
2 5
4 2
... ...
now i want to get the name of each product so it whould be like:
FirstCode,FirstName,SecondCode,SeconeNam
the names stored in other table.
how can i do it?
thanks
Dovalle
View 1 Replies
View Related
Oct 13, 2015
I've got a select as follows:
select computer, count(*) as MissedCount from WInUpdates_Neededreq
WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE())
group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
View 16 Replies
View Related
Jun 16, 2004
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
View 1 Replies
View Related
Jul 15, 2014
I have a table has following fields(Process, datestart,dateend,datedue,,,,)
Process datestart dateend ...
A 14.07.2017 20.07.2014
B 15.07.2014 21.07.2014
C 24.07.2014 30.07.2014
What i want when user enters from date and end date. i want to filter records which are fallen between those two input dates.
if user enters 17.07.2014 and 20.07.2014 as input range. query should show only A and B process as
A 14.07.2017 20.07.2014
B 15.07.2014 21.07.2014
View 3 Replies
View Related
Apr 27, 2015
I have table with filed
EmpProject -Table
Empno, Name, Project NO, cost center
I need to restrict the results by project No by user input. In case if user did not provide any value for the projectNO, then need to fetch all rows.
Select empno, name, projecno, cost_center from empproject where projectno=nvl(:pno, :deptno)
View 6 Replies
View Related
Nov 14, 2006
Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters.
Something like this:
create storeproc
Param1, param2
if param1 <> null
Select *
from table
where id = param1
else
Select *
from table
where id = param2
end if
Thanks in advance....
View 2 Replies
View Related
May 6, 2015
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
View 4 Replies
View Related
Oct 20, 2007
I am using the lookup transform with the following settings:
Reference table: Use results of an SQL query. The query retrieves the surrogate key and four business key columns from a dimension table which contains a few thousand rows.
Columns: business keys in the incoming data are mapped to the business keys in the reference table, and the surrogate key is looked up from the reference table.
Advanced: Enable memory restriction is OFF (and the other items on the Advanced tab are greyed out).
I assumed that this means that the lookup transform would cache all of the rows in the SQL query, and then perform the lookups against this cache. This is the behaviour that I saw when I was running the package in my local environment in the BIDS debugger.
However, a colleague was doing some profiling on our production database server, and noticed that the lookup transform is instead issuing a single SQL query for each row of input. Our production ETL server has many GBs of free RAM available (way more than enough to cache the contents of the lookup table in memory), and given that memory restriction is disabled, I don't understand why the lookup transform is behaving in this fashion. Does anyone have an explanation for this? I'm probably misunderstanding a key concept here.
Thanks,
Greg
View 8 Replies
View Related
Nov 16, 2007
I have one query which uses a join query to gather all the projects that should show up in someone's list over a period of time (returns and id (int) and name (varchar) paired dataset). I want to do a separate query that takes that list and selects all projects (same paired set ... id and name) EXCEPT where it matches an id on a row of the given result set. The one query looks like this ..DECLARE @startDate datetimeDECLARE @endDate datetimeDECLARE @userId UNIQUEIDENTIFIERSELECT @startDate = ppStartDate FROM ppTablewhere payPeriodID = @payPeriodIDSELECT @endDate = ppEndDate FROM ppTable WHERE payPeriodID = @payPeriodIDSELECT @userId = userID FROM usersTable WHERE userName = @userNameSELECT DISTINCT p.projectID, p.projectNameFROM projectsTable pLEFT JOIN projectMemberhsip m ON m.ProjectId = p.ProjectIdLEFT JOIN timeEntryTable t ON t.ProjectID = p.ProjectIdWHERE t.TimeEntryUserId = @userID AND t.TimeEntryDate >= @startDate AND t.TimeEntryDate <= @endDateORm.UserId = @userID I want to get the same selection from projectsTable WHERE it's not anything from this result set.Haven't been able to get it by modifying the WHERE logic. Is there a way to select all WHERE id != (resultSet from this SELECT)? TIA!
View 5 Replies
View Related
Aug 23, 2006
I am trying to round this data, but can't find a good resource on this. If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.ROUND(DATEDIFF(mm, employee.emp_begin_accrual, GETDATE())
* employee.emp_accrual_rate - SUM(ISNULL(request_1.request_duration, '0')), .5) Any help would be greatly appreciated. A link to a good reference on rounding would help too.Thanks in advance!!!
View 8 Replies
View Related
Mar 29, 2013
I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:
SiteDate Data
A01/01/2013 ...
B02/01/2013 ...
C03/01/2013 ...
A04/01/2013 ...
B05/01/2013 ...
C06/01/2013 ...
And so on...
In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.
View 3 Replies
View Related
Jul 23, 2005
I want to create a stored procedure that returns a list of records froma table. But depending on a userID value given only certain recordswill be returned that they have access to.I think this might be hard to do in a single SELECT statement becausethe user might also belong to a group that might have permission, etc.Can you do something like this pseudo code in a T-SQL procedure?DECLARE cur CURSOR FOR SELECT * FROM myTableOPEN curFETCH NEXT FROM curWHILE @@FETCH_STATUS = 0BEGINif( accessGranted(curRecord.id) ){ addRecordToResultSet() }else { ommitRecordFromResultSet() }END
View 11 Replies
View Related
Apr 25, 2008
Hello there,
I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table:
1- the exact match for the search string come first from the two table.
2- and the partial match comes last after the exact match.
this is my DDL for the two tables :
USE [Northwind]
GO
/****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
second table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
[MemberID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
and this my search query I have it in a stored Proc.
select *
from
(
SELECT PersonID, Type, Name, email
FROM Person
WHERE (Name LIKE '%'@Name'%')
union all
SELECT PersonID, Type, Name, email
From Members
WHERE (Name Like '%'@Name'%' )
) Y
Order by
Case[Name] when @Name Then 1 Else 2 End,
Case[Name] when 'm' Then 1 Else 2 End
Thank you for your time
Sms
View 5 Replies
View Related
Jan 18, 2006
Hey everyone,
I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.
The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.
Here is what I am wanting to accomplish:
UPDATE tblWorkOrdersSET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)
I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.
I would appreciate any suggestions.
Thanks,
Lee.
View 2 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Aug 28, 2006
I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page. I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.
View 1 Replies
View Related
Nov 7, 2007
my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader(); can we have something likeHashtable = cmd.ExecuteReader();
View 5 Replies
View Related
Dec 2, 2003
Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?
View 2 Replies
View Related
Jun 17, 2004
Hi,
I need to keep track of the number of hits on a particular page. Im using a stored Procedure
What I want to do is get the number of hits and increment it by one :)
ie: Sub Procedure should be like below
SELECT noOfHits WHERE pageName = 'bla bla'
noOfHits = noOfHits + 1 etc.
Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'
What I need to do in essence is:
1. Check that a particular row exists. if it doesnt create it.
2. Increment a value (by one) to a column in this particular row.
Phew. Hope you got that. Any ideas much appreciated,
Thanks,
Pete
View 4 Replies
View Related
Jan 18, 2003
Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.
I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.
I tried the follwoing, but get incorrect results.
select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price
However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.
select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price
I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?
View 2 Replies
View Related
Aug 30, 2004
I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.
So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.
Like I said I am new and this is probably very simple but i need a little help getting started
thanks
View 6 Replies
View Related
May 28, 2013
way to insert a summary row where there are no query results - in effect a row to say there were no results for today. I am using SQL Server 2008.
I have created a report which uses 2 tables to store all daily transactions & a one row summary of those daily transactions for upto 4 countries - both tables are updated nightly by 2 SP and the unique identifier is a field called ID which contains the country code and date;
table1_detail;
store all daily transactions for 4 countries and the unique identifier is an ID which stores the country code and date (XX_ddmmyy) eg GB_280513. There may be transactions for upto 4 countries(GB/FR/IE/DE) per day or there may be none.
Table1:
Code:
ID DATE CUST VALUE ERROR ...etc
GB_280513 280513 101 10.50 YES
GB_280513 280513 102 90.00 NO
FR_280513 280513 201 25.00 NO
IE_280513 280513 301 60.00 NO
FR_280513 280513 202 10.50 YES
FR_280513 280513 203 10.50 NO
GB_280513 280513 103 20.00 YES
GB_280513 280513 104 5.00 YES
table2_summary;
summary of daily transactions per unique identifier (XX_ddmmyy) in table1_detail. When there are transactions per unique identifier in table1 the SP inserts a row summarising quantity, value, error count etc.
Table2:
Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0
I need the insert into table2_summary to be able to insert a row everyday for each country showing zeros where there were no transactions for that day (ID) in table1;
Table2 expected results:
Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0
DE_280513 280513 0 0.00 0
When there are no transactions in table1 for a day (ID) then the summary needs 4 zero rows inserted.
View 3 Replies
View Related
May 19, 2008
In my select statement I concatenate city+state+zipcode. In some cases the zipcode is 9 digits but is missing the '-'. How can I format zipcode to be '#####-####' when > 5 characters from the select statment.
Thanks,
Ken
View 1 Replies
View Related
Jun 6, 2008
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)
Looking to run a query that will retrieve the number of results returned from a select statement...
Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:
SELECT * FROM Software WHERE LicensesID = 2
Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?
Thanks all!
View 2 Replies
View Related
Dec 16, 2013
I have a table with three columns. Name, book#, category.
I need to write a select statement that looks at the book# and category and returns all the names that match that into one joined our combined result. So if my name column had john, Jane, Jim, jack, and Steph in unique rows that matched it would bring back a resulted formatted as. John, Jane, Jim, jack, Steph. So I could place that in a field for reference.
View 4 Replies
View Related
Jul 14, 2014
Just wondering if it's possible to select distinct results from the where clause?
View 3 Replies
View Related
Nov 20, 2014
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"..It is about a Stored Procedure, which not only should output the number of opportunities with status "Pending" or "Accepted" but also the name of the opportunities with these statuses. Following points:
1. The SELECT that outputs the names of the opportunities may in some cases return more than one value. I created then the CURSOR CUR_TEST.
2. The code should return the names of Opportunities, namely after "The opportunities are:" and these must be sent to the recipients of an email. How can I do this, so the Names of the Opportunities and the rest of the email can be sent by email?. I mean, the concatenation with SET or SELECT doesn't work. I get as email only the output of the last row of the SELECT with cursor CUR_TEST.
3. I add an example how the output should be.
The following is the code:
BEGIN
declare
@V_USER VARCHAR(20),
@V_NAME VARCHAR(100),
@V_QUANTITY INT,
@V_EMAIL VARCHAR(60),
@V_BODY VARCHAR(MAX),
@V_QUANTITY_ACTIVE int,
[code]...
An example how the Output in the email body should be:
Dear MyName,
You have 47 Opportunities with status Pending.
The Opportunities are:
Name of Opportunity 1
Name of Opportunity 2
Name of Opportunity 3
View 1 Replies
View Related
May 29, 2007
In other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
View 2 Replies
View Related
Jul 23, 2005
I'm sure this is an obvious question but much of SQL is new to me.I have a stored procedure and I want to use the results of a selectstatement elsewhere in the stored prcedure. The select statement willreturn at most one record and only one column, here's an example :select top 1 Sequence from MyTablewhere ParentId=4 and Sequence > 200 order by sequenceCan I put this result in a variable? Do I have to use SELECT INTOTempTable and refer to TempTable and then drop the table at the end ofthe stored procedure? I have read a little about cursors, are thesethe way to go?I'm confused as to what my options are, any help or links to help willbe appreciated.Thanks for reading.
View 1 Replies
View Related
