sql

How to convert this SQL so that it uses less subqueries?

SELECT s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding, (SELECT titel FROM serie_details WHERE taalcode = 'oo' AND serie_id = s.id) AS titel, (SELECT beschrijving FROM serie_details WHERE taalcode = 'oo' AND serie_id = s.id) AS beschrijving, ARRAY_AGG(d.titel) AS alt_titels, (SELECT ...

Export large amount of data from Oracle 10G to SQL Server 2005

Dear all, I need to export 100 million data rows (avg row length ~ 100 bytes) from Oracle 10G database table into SQL server (over WAN/VLAN with 6MBits/sec capacity) on a regular basis. So far, these are the options that I have tried and a quick summary. Has anyone tried this before? Are there other better options? Which option would be...

Can MS report data be redirected?

Can MS report data be redirected? I have a MS report control and I want to be able to take the data that would go to the (MS report control) UI in some cases to instead of being viewed, go to a file. I don't see where that can be done but want to ask if anyone knew a way. I tried look at the datasource from the report but I don't see ...

SQL Server INSERT ... SELECT Statement won't parse

I am getting the following error message with SQL Server 2005 Msg 120, Level 15, State 1, Procedure usp_AttributeActivitiesForDateRange, Line 18 The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. I have copy and pasted the select...

Why does RIGHT(@foostr, 0) return NULL when @foostr is varchar(max)?

In SQL Server 2005 If I want to find the right-most one character of a varchar(max) variable, no problem: declare @foostr varchar(max) set @foostr = 'abcd' select right (@foostr, 1) ---- d If I want to find the right-most zero characters of a string literal, no problem: select right ('abcd', 0) ------------------ It returns an e...

Generate T-SQL for Existing Indexes

How do you programmatically generate T-SQL CREATE statements for existing indexes in a database? SQL Studio provides a "Script Index as->Create to" command that generates code in the form: IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = N'IX_myindex') CREATE NONCLUSTERED INDEX [IX_myindex] ON [dbo].[mytable] ( [my_...

An expression of non-boolean type specified in a context where a condition is expected, near 'END'.

So maybe someone can point me in the right direction of what is causing this error? I've been fighting with this for a couple of hours and searching the web, and I can't figure out what I'm doing wrong here. It's included as part of a stored procedure, I don't know if that matters, if it does I can include that as well. Tables and fie...

Help needed with MySQL query to join data spanning multiple tables with data used as column names

I need a little help putting together a SQL query that will give me the following resultsets: and The data model looks like this: The tricky part for me is that the columns to the right of the "Product" in the resultset aren't really columns in the database but rather key/value pairs spanned across the data model. Table data i...

Suggest an alternative way to organize/build a database solution.

We are using Visual Studio 2010, but this was first conceived with VS2003. I will forward the best suggestions to my team. The current setup almost makes me vomit. It is a C# solution with most projects containing .sql files. Because we support Microsoft, Oracle, and Sybase, and so home-brewed a pre-processor, much like C preprocessor, ...

Loop through non-integer rows using SQL

I know how to accomplish my task with .NET, but I wanted to do this just in SQL. I need to loop through all of the rows where the primary key is somewhat arbitrary. It can be a number or a series of letters, and probably any number of unusual things. I know I could do something like this... DECLARE @numRows INT SET @numRows = (SELECT ...

SQL Alter Table to Add a Column, does it do anything "scary"?

I need to add a column to an existing table in my live sql database. I know how to use the ALTER command, but what I want to know is the effect this has on the table. For example,, if using SQL Management Studio, it claims that adding a column will "drop and recreate" the table. Will the ALTER table command do that as well? This table...

How to select first entry of the day grouped by user in SQL

I've looked around and can't quite grasp the whole answer to this SQL query question needed to extract data from an MS Access 2000 table. Here's an example of what the table [Time Sub] looks like: **CLIENT_ID, DATE_ENTERED, CODE, MINUTES** 11111, 5/12/2008 3:50:52 PM, M, 38 11111, 5/12/2008 2:55:50 PM, M, 2 11714, 5/13/2008 1:15:...

Archive Parent / Child Table Hierarchy in MySQL

If I have a Parent and Child table in MySQL related by a foreign key, is it possible using a SQL statement to move certain rows from Parent and the related rows from Child into archive tables (e.g. Parent_Archive and Child_Archive) in an atomic manner? ...

Date arithmetic using integer values

Problem String concatenation is slowing down a query: date(extract(YEAR FROM m.taken)||'-1-1') d1, date(extract(YEAR FROM m.taken)||'-1-31') d2 This is realized in code as part of a string, which follows (where the p_ variables are integers, provided as input by end users): date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day...

ORACLE SQL ROWNUM execution order

Dear expert, in Oracle SQL, there is a possible criteria called rownum. Can i confirm that rownum will be executed at last as just a limit for number of records return? or could it be executed first, before other WHERE SQL criteria (let's if we put rownum prior to the others)? ...

How to figure out which record has been deleted in an effiecient way?

Hi, I am working on an in-house ETL solution, from db1 (Oracle) to db2 (Sybase). We needs to transfer data incrementally (Change Data Capture?) into db2. I have only read access to tables, so I can't create any table or trigger in Oracle db1. The challenge I am facing is, how to detect record deletion in Oracle? The solution which I ...

rank on two dates - each date iteratively

How to query for rank over 'value' for each day in the below table? Ex: IT should list out the 'mydate', 'value', 'rank' for all values on 20th and then do a fresh rank() for all values on 21st? Thanks... create table tv (mydate,value) as select to_date('20/03/2010 00','dd/mm/yyyy HH24'),98 from dual union all select to_date('20/03/2010...

how to make condition to update table ?

hi..i want to make condition to update my table if there's already same data (in the same column) inserted in the table. im using If String.ReferenceEquals(hotel, hotel) = False Then insertDatabase() Else updateDatabase() End If this is the updateDatabase() code... Dim sql2 As String = "update infoHotel ...

Check if table exists in c#

I want to read data from a table whose name is supplied by a user. So before actually starting to read data, I want to check if the database exists or not. I have seen several pieces of code on the NET which claim to do this. However, they all seem to be work only for SQL server, or for mysql, or some other implementation. Is there not...

differentiating results of sql right join

Hi I have a below SQL query SELECT `User`.`username` , Permalink.perma_link_id, Permalink.locale, Permalink.title, DATEDIFF( CURDATE( ) , Permalink.created ) AS dtdiff, `TargetSegment`.segment_text, TargetSegment.source_segment_id, TargetSegment.perma_link_id ,TargetSegment.created , TargetSegment.updated, DATEDIFF...