sql

SQL Server Foreign Key constraint benefits

We're designing a database in which I need to consider some FK(foreign key) constraints. But it is not limited to formal structuring and normalization. We go for it only if it provides any performance or scalability benefits. I've been going thru some interesting articles and googling for practical benefits. Here are some links: http:/...

SSIS: How to delete rows from a resultset before using the OLE DB Destination?

Hello! I have a problem using the SSIS. I try to import data from database one to database two. Therefore I use a OLE DB Source with the following command: SELECT Name, Description, JobID FROM DBone.Table My problem is, that I only want to import new data from this source. So I use a second OLE DB Source withe the following command...

How do I convert a nested select to joins when nested select has a temp table?

I have the following SQL. Tables defined. #tbContinent -------- ContinentID | ContinentName AddressInCountry -------- AddressID | CountryID AddressInContinent -------- AddressID | ContinentID NameInAddress -------- AddressID | NameID I then have the following SQL: --- Insert into tbName DECLARE @tbName TABLE ([ID] int, [Add...

"SELECT TOP 1" equality for codeigniter?

Hi Friends, I need to get only 1 record from sql result. we use "SELECT TOP 1" in standard sql, but how can we do that in CodeIgniter? Is there any func for that? I researched so much on net, but could not find :/ appreciate! thanks, ...

SQL Server: Serial number generator implementation question

Hi folks: My clients need a format of serial #: YYYYMMDD098 where YYYYMMDD represents today, 098 is a 3-digit number that will get increased by 1 every time we access the serial #. Furthermore, the 3-digit # will trun back to zero every day. Where can I find a reference for this solution? Thanks. ...

SQL : BETWEEN vs <= and >=

In SQL server (2000 & 2005 ) What is the difference between these two queries ? Which one I should opt on which scenarios ? Query1: Select EventId,EventName from EventMaster where EventDate BETWEEN '10/15/2009' and '10/18/2009' Query2: Select EventId,EventName from EventMaster where EventDate >='10/15/2009' and EventDate <='10/18/...

SQL: datetime or varchar as parameter datatype for stored procedure

I have a SQL server stored procedure which would accept date as input param to build a query in it.So which datatype i should use for the parameter when defining stored procedure.Whats the difference between using nvarchar(15) and datetime . 1 : create procedure TestProcedure(@startDate datetime) and 2 : create procedure TestP...

How to write a search query for this form

I want to write a search query that search on the criteria like city, rent, area. i am new to database and sql queries. How to write query. Please help. Any suggestions will be highly appreciated. My form snapshot is here ->> ...

If Record Exists, Update Else Insert

Hi there, I'm trying to move some data between two SQL Server 2008 tables. If the record exists in Table2 with the email from Table1 then update that record with the data from Table1, else insert a new record. In Table1 I have a number of columns; firstname, surname, email and so on. I'm not quite sure how to structure the query to up...

SQL Update columns passing into the query the column name and value

Hi, I have the following code: UPDATE myTable SET Col1 = @Value However, I have a table that has over a 100 columns and want to be able to specify a column name by passing the name into the query similar to: UPDATE myTable SET @ColName = @Value When I do this I get an error. Is there a good solution to this? Its probably s...

how do I remove nested select statement

Hi. I have a Name table with the columns NameID Name TypeID With the following SQL SELECT[NameID] FROM[Name] WHERE[TypeID] = @TypeID AND NameID >= (SELECT MIN([NameID]) FROM [Name] WHERE [Name]='Billy' AND [TypeID]=@TypeID) Ive been asked to convert this to an Inner Join without using any nested select but n...

SQL DELETE performance problem

delete from a A where a.ID = 132. The table A contains around 5000 records and A.ID is the primary key in the table A. But it is taking a long time to delete . Sometimes its getting timed out also . That table contains three indexes and it is referenced by three foreign keys . Can anyone explain me why its taking long time eventhough w...

Reporting Services - Working with the same reports accross different databases

I have a Reporting services project that has a number of reports in the the report folder and a Live, Test and Dev data source in the Shared Data Source folder. I have set up the solution configurations to deploy to different folders on the server based on the config (eg. debug -> dev). My problem is that i need the reports in the dev ...

MySQL merging two queries

I have the following table setup: branches table: ... manager_id sales_manager_id admin_manager_id ... the above-mentioned columns contain the foreign key for the users table. to complicate things I also have a branches_extra table: branches_extra: id branch_id user_id position_id which contains user_id, branch_id foreign keys f...

ACCESS 2007: Determine maximum value per row across multiple columns

I have a table with multiple date columns per employee: Emp 1, Date1, Date2, Date3 Emp 2, Date1, Date2, Date3 etc. I would like to write a single query that returns to me the maximum date of the three dates available on a per-employee basis. I understand I need to write a UNION ALL query but I can't seem to get the syntax so t...

Can I gracefully include formatted SQL strings in an R script?

I'm working in an R script that uses a long SQL string, and I would like to keep the query relatively free of other markup so as to allow copying and pasting between editors and applications. I'd also like the ability to split the query across lines for better readability. In the RODBC documentation, the paste function is used to build...

mysql format results as columns not rows...

Hi, Using MySQL Server, I have a table structure like: (Table test) id Name 1 "test1" 2 "test2" 3 "test3" When I perform the following query: "Select name from test", the results are: "test1" "test2" "test3" How can I adjust this query so the results are columns, as such: "test1" "test2" "test3" ...

Sybase openqueries ?

Hello, Does open query exist in sybase ? Or more generally, in sybase, what are the possible way to select among the result of a procedure (temporary tables, out params, others ??) ...

Looking for SQL constraint: SELECT COUNT(*) from tBoss < 2

Hi, I'd like to limit the entries in a table. Let's say in table tBoss. Is there a SQL constraint that checks how many tuples are currently in the table? Like SELECT COUNT(*) from tBoss < 2 Firebird says: Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 3, column 8. SELECT. Thanks. Norbert ...

Surrogate key as a foreign key over composite keys

I realise there might be similar questions but I couldn't find one that was close enough for guidance. Given this spec, Site --------------------------- SiteID int identity Name varchar(50) Series --------------------- SiteID int SeriesCode varchar(6) ... --SeriesCode will be unique for every unique SiteID Episod...