I am managing a data warehouse. I have several dimension tables most of which are type-2 and some are type-1. I was able to figure out how to use MERGE to maintain my type-1 dimension tables. I am stumped on how to do the type-2 dimension tables. How do I do both, the update to the EndDate and an insert using the MERGE Command? ...
Is it possible to express 1 or 0 as a bit when used as a field value in a select statement? e.g. In this case statement (which is part of a select statement) ICourseBased is of type int. case when FC.CourseId is not null then 1 else 0 end as IsCoursedBased To get it to be a bit type I have to cast both values. case when FC.Course...
[noob warning!] I need to store some data in some tables where it is like the equivalent of an array of pointers to polymorphic objects. E.g. (pseudo C++) struct MyData { string name; } struct MyDataA : MyData { int a,b,c; } struct MyDataB : MyData { string s; } MyData * data[100]; I don't really know what google search to enter! How ...
Here's an obfuscated version of something I've been trying to do at work. Say I have been given this month's data for customers in my shop - how much they've spent split by the food type: CUSTOMER FOOD_TYPE FOOD_TYPE_VALUE 1 SWEET 52.6 1 SAVOURY 31.0 1 DAIRY 45.8 1 ...
I have a table of all Major League Baseball games that is structured like this: **Game** id (int) home_team_id (int) away_team_id (int) home_score (int) away_score (int) date (date) I also have table named "ticket" that has tickets sold to different games: **Ticket** id (int) game_id (int) price (float) time_sold (datetime) I'd lik...
I am creating a database that tracks replays for a game. Each replay has a different game mode that is either team-based gameplay or individual-based gameplay. Depending on the game mode, I want to record the winning team or the winning individual. I have the following MySQL table that tracks rounds in the replay and the associated winn...
I know just enough about SQL tuning to get myself in trouble. Today I was doing EXPLAIN plan on a query and I noticed it was not using indexes when I thought it probably should. Well, I kept doing EXPLAIN on simpler and simpler (and more indexable in my mind) queries, until I did EXPLAIN on select count(*) from table_name I thought ...
I was wondering if there was a way to not execute my subquery if my @ID1 is NULL? CREATE PROCEDURE [dbo].[TestTable_Search] @Col1 int, @Col2 uniqueidentifier, @Col3 datetime, @Col4 datetime, @ID1 varchar(10) AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM [dbo].[TestTable] WHERE [Col1] = CO...
Here's the basics of the system we have in place today. We have a SQL 2005/2008 table defined as: CREATE TABLE [dbo].[Profiles] ( [Firm] [char] (4) NULL , [Account] [char] (10) NOT NULL , [UndSym] [char] (24) NOT NULL , [Updated] [timestamp] NOT NULL , [Data] [image] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ...
Can someone tell me what the most efficient way to transfer SQL Jobs from SQL Server 2005 to sql 2008. I'm currently upgrading my sql servers to 2008. Thanks! ...
Hi, I have a weird problem. My query in C#/ASP.NET returns results 5 times. I tried brakepoint-ing but I can't find the error. I have 2 related tables. One table loads on PAGE_LOAD and when the user click on a cell, it shows the content from another table related to that cell. It's very simple. //PAGE LOAD protected void Page_Load(o...
I'm not an expert with either System.Data nor SQL Server, but have a need to generate a large DataSet then use System.Data.SqlClient.SqlBulkCopy to store the results. The DataSet will consist of about 10 related tables. When SqlBulkCopy transfers the DataSet to SQL Server, my understanding is that rows from the various DataTables will ...
This is my SQL Statement UPDATE sysdba.CONTACT, sysdba.ADDRESS SET sysdba.ADDRESS.Address1 = '123 Tech Parway' WHERE sysdba.CONTACT.AddressID = sysdba.ADDRESS.AddressID AND sysdba.CONTACT.contactID = 'CRBD' What is wrong here? Thanks in advanced! EDIT: Using SQL Server 2005 ...
When performing UPDATE and INSERT queries using Zend_Db, I frequently need to set values equal to NULL (not ''). However, the default behavior of Zend_Db::insert() and Zend_Db::update() seems to be that values that are empty are translated into empty strings ('') and put into the database as such. Does anyone know of way to actually fo...
Excel 2007 with Data in SQL 2008 View. Trying to create a PivotTable from an external datasource (SQL 2008 - View). I would like to create a filter for a date range, since the view contains a lot of records. The problem is I can't figure out how to specify a filter for my view so the filtering takes place in SQL. Ideally user would be ...
I have a table with some rows in. Every row has a date-field. Right now, it may be duplicates of a date. I need to delete all the duplicates and only store the row with the highest id. How is this possible using a SQL query? Now: date id '07/07' 1 '07/07' 2 '07/07' 3 '07/05' 4 '07/05' 5 What I want: date id '07/0...
Hi. I'm pretty new to SQL world. Here are my questions: What are the benefits of stored procedured over normal SQL statement in applications? Does stored procedure help eliminates SQL injection? In Microsoft SQL Server it is called stored procedure. How about in Oracle, MySQL, DB2, etc.? Thanks for your explanation. ...
I have a trigger that supposed to update log out time[generate random log out time within an hour of log in time, seconds have to differ too], how do I achieve that functionality, please help here is my trigger so far: USE TestDB GO CREATE TRIGGER AfterInsertTG ON dbo.usage_reports_Dummy2 AFTER INSERT AS DECLARE @pk_id as int, @mem...
hello, I've got a winform with some child forms on it. Now I'm accessing the child forms by a treeview in my main form. Now in that treeview is a list of websites. Since a website is actually a group of pages, this is also shown in the tree. Website is my parent and the pages are the childs. When you click on one of the pages the stati...
Any ideas on how to accomplish this? USE [db_name] BEGIN TRANSACTION ...TONS OF INSERTS COMMIT; RAISERROR (..) WITH NOWAIT; //If error continue with next batch BEGIN TRANSACTION ...TONS OF INSERTS COMMIT; RAISERROR (..) WITH NOWAIT; ... ...