sql

CONCAT'ing NULL fields

I have a table with three fields, FirstName, LastName and Email. Here's some dummy data: FirstName | LastName | Email Adam West [email protected] Joe Schmoe NULL Now, if I do: SELECT CONCAT(FirstName, LastName, Email) as Vitals FROM MEMBERS Vitals for Joe is null, as there is a single null field. How do you ov...

possible to insert the 1st of n tables from a proc into a temp table?

I think the answer to this is no going in, but I could use a sanity check. I have a proc that returns 3 tables. Table1 has 23 columns. Table2 has 12 columns. Table3 has 2 columns. I need to run this proc and dump the results of Table1 into a temp table. I created a @table and then did this: insert @myTable exec [myDb].dbo.[multitabl...

SQL to Return Results from Week 13 Year 2008 (not grouped)

Hi folks, I'm trying to use a Year-Week format in oracle SQL to return results only from a range of Year-Weeks. Here's what I'm trying SELECT * FROM widsys.train trn WHERE trn.WID_DATE>=TO_DATE('2008-13', 'YYYY-IW') AND trn.WID_DATE<=TO_DATE('2008-15', 'YYYY-IW') ORDER BY trn.wid_date but it shoots this error. ORA-01820: format c...

Is it possible to add a logic Constraint to a Foreign Key?

Hi folks, I've got two tables and I've added a foreign key constraint. Kewl - works great. Now, is it possible to further constrain that relationship against some data in the parent table? Basically, I have animals in the parent table, and for the child table wishto only contain data where the parent data are .... um .. mammals. eg. ...

Upgrading SQL Server 2000 to MySQL 5.1 using DTS - How solve DateTime problem?

I'm trying to migrate from sql server 2000 to mysql by using DTS. That's the best tool I know yet. But one a sql server tables has a column with datetime type, the DTS suggest the following sql code: `StartDate` long varbinary NULL, `EndDate` long varbinary NULL, It should be: `StartDate` DateTime NULL, `EndDate` DateTime NUL...

MS Access linked to SQL server views

Hello, we have an issue with an access database we are upgrading to use SQL Server as its data store. This particular database links to 2 sql databases, so I thought to simplify things, we have a view in the main database that linked to each table in the secondary database. That way access would only need to talk directly with one SQL...

string array -> longId array in a SQL statement

I am getting a string from HttpContext.Current.Request["key"] which i then do split(',') if its not null. Now i would like to write a sql statement deleting any msgId (long/Int64) that matches the keys, how should i write it? The only way i unserstand is to convert the string[] to long[] then loop a SQL statement. Is there a better way?...

T-SQL: How to use parameters in dynamic SQL?

I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER. When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody help? CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */ ( @p_Creat...

Is it bad to use a Sql Server Scalar UDF in this scenario?

Hi Folks, I'm trying to generate some sql that is used to calculate some final scores -> think kids at school and their end of year scores. I was going to have about 5 or so Scalar UDF's that, accept some simple values (eg. current score, subjectid, whatever) and then spit out a decimal value. eg. CREATE FUNCTION [dbo].[GetRatingModi...

SQL LEFT JOIN return 0 rather than NULL

Hi I want to join two tables, with the number of records for each type being counted. If there are no records of that type in the left table I want a 0 to be returned, not a null. How can I do this? Karl ...

Most Efficient MySQL query to update a table from identical memory table

I'm implementing a memory cache for a table that looks like this (simplified): Item1 (integer), Item2 (integer), cnt (Integer) The original table includes millions of pairs like this. and it updates rapidly. To make it all more efficient I want to write new pairs to an identical memory table and update the real table on disk periodica...

Is a Windows Service application the right approach

I currently have a windows application which is automated and runs daily. the purpose is to access a webservice to download a dataset, inserting into sql 2005 database. Is a windows service application suitable in this situation, would it be more flexible, and would it perform better. ...

Getting Login error in sql server 2008

Hi All, I have SQL server 2008 and it was working fine ,but today while starting SQL server I am getting the following error : "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server i...

Can I use SELECT statement inside INSERT values?

I tried this: INSERT INTO tbl_vaucher ( vaucher_name, created_date ) VALUES ( ( SELECT TOP 1 con_full_name FROM tbl_contact ), GETDATE() ) , getting: Subqueries are not allowed in this context. Only scalar expressions are all...

How to find current transaction level?

How do you find current database's transaction level on SQL Server? ...

SQL Pivot based on value between two columns

I have a table in which I have among other things, two columns, one for start date and another for end date. I need to write a query which will return a column for each month of the year and the value of that column is 1 if the month is between 0 otherwise. The PIVOT statement seems to be what I am looking for here, but from the best I...

SQL conditional SELECT

Hi I would like to create a stored procedure with parameters that indicate which fields should be selected. E.g. I would like to pass two parameters "selectField1" and "selectField2" each as bools. Then I want something like SELECT if (selectField1 = true) Field1 ELSE do not select Field1 if (selectField2 = true) Field2 ELSE do no...

Efficient way of getting @@rowcount from a query using row_number

I have an expensive query using the row_number over() functionality in SQL Server 2005. I return only a sub list of those records as the query is paginated. However, I would like to also return the total number of records, not just the paginated subset. Running the query effectively twice to get the count is out of the question. Sele...

self join table - performance/other implications

Are there any performance/other implications in having an object relates to itself? (self join) Consider the following example: PEOPLE (table name) belongs_to :profile, :class_name => 'Person', :dependent => :destroy id login password first_name last_name profile_id This question is party stemmed from another question posted at http:...

SQL - Check table for new rows?

I have two tables, for example: Table A Table B ======= ======= Name | Color Name | Color ---------------------- ---------------------- Mickey Mouse | red Mickey Mouse | red Donald Duck | green ...