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.
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!
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.
I am taking details from a linked server file (.XLS file). Two of the fields in this file are STARTTIME and STARTDATE.
The problem I have is that these have to be imported into a SQL table, but only into one column of type DATETIME. The column name is STARTDATE. I really have no idea on how to go about this. Any ideas would be greatly appreciated.
Hello,How do I concatinate a variable. Here's the scenarios:declare @var1 varchar(20)declare @var2 varchar(20)declare @var3 varchar(20)declare @var4 varchar(20)....declare @var32 varchar(20)set @var1 = 'Something 1'set @var2 = 'Something 2'....set @var32 = 'Something 3'/* I have to store the values of these individual variables. I wish tohave a "While" routine which iterates through the above variables. Iwish to have the variable name concatinated as that I do not have towrite numerous lines of code setting up individual 32 variables. Howcould I use the '+' operator to join 'var' + @count . Where count isfrom 1 through 32. I am having some trouble with the syntax.*/Regards,VS
Newbie question regarding a db I have inherited. A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.DocNo has numbers that represent persons. SequenceNo has numbers that represent specific documents associated with each person (DocNo).So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.My SELECT statement looks like this:SELECT ReadingNo, SequenceNoThis returns data like this: 5 3I would like to concatenate the SELECT statement to return like this: 5-3So I made Sql like this:SELECT ReadingNo + '-" + SequenceNo Which returns a alias ('No Column Named') result value of 8 which is an arithmetic result instead of a string concatination that I want.So my questions are:1. Should the original database designer have used string data types for these columns since they will never be used for math purposes?2. Do I need to cast them to string data type (like nchar(4) - since neither column will ever exceed 4 digits) to get the result I desire?3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?
Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA
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...
Using gridview to display the data and sql server 2000 I have a column in the database say departtime of datetime datatype that cntains the date and time resp(09/19/2007 9:00 PM). I am separating the date and time parts to display in two different textboxes say txt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by using the convert in sqldatasource. Now i need to update the column in the database and i am using Updatecommand with parameters in aspx lke updatecommand = "Update table set departtime = @departtime" . How can i update my column as datetime by getting the data from 2 texboxes as now i have 2 textboxes displaying data for single column means if user edit the data in txt1 as(10/19/2007) then on click of update i need to populate the column daparttime as (10/19/2007 9:00 PM).Please let me know if you have any questions.
I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:
SET CONCAT_NULL_YIELDS_NULL ON
Here's the code snippet:
select DISTINCT
(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +
(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +
(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +
(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +
(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'
The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.
Any thoughts or ideas would be greatly appreciated.
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!
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!!!
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...
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.
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
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
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?
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.
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();
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?
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,
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?
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
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.
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
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?
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.
"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.
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.
