I have a table. The primary key is id and its auto incremented. Now when I insert a new record, I need to get the id with which the record was updated. How can I do that? If i use the query... select max(id) from table_name ...after executing I can get the id. But can I be sure that its the id of the record that was just inserted? ...
How do i do select p.Quota without writing the long full name? right now i am doing SELECT c.id, c.UserName, p.Quota, cs.StatusName FROM CUSTOMERS AS c, PRODUCTS AS p LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId LIMIT 1 ; I get the error: ERROR 1054 (42S22): Unknown colu...
I have a loginAudit table and I am trying to get a count for all logins for each day. What I'd like to do is have days where there are no logins return their day and a login count of 0. Currently no row is returned for days with no logins. Could be this isn't possible and have to fill in empty groups in the app after query results re...
I am trying to implement an algorithm in SQL (transact sql) and finding it difficult given my current abilities. I have tried to strip the problem down to the esstentials. The basic idea behind this algorithm is that a user is planning out their budget for lets say a month. They have a good idea of both how much and when money is coming ...
Hello. Here is my query: SELECT student_id, section, count( * ) as total FROM raw_data r WHERE response = 1 GROUP BY student_id, section There are 4 sections on the test, each with a different number of questions. I want to know, for each student, and each section, how many questions they answered correctly (response=1). However, with...
I'd like to clear a database's schema on my SqlServer instance. What tsql should I use? By schema I mean tables, constraints, etc. I want the result to be similar to if I created a new database, however I don't want to actually drop and create the database. Why: For those curious, I need to empty the schema without dropping because of...
I have been trying to adapt my code (a webservice) to get the SQLServerMessages: SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString); //capture the infomessage event to capture c.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e) { serverMessages += e.Message; //"\n" + e...
I have a string from oracle table like this: SELECT col1 FROM MyTable; col1 part1;part2;part3 .... How can I get a substring from col1 such as part2? SELECT col1, INSTR(col1, ';', 1, ...) SubStrPart2 // string func? col1 SubStrPart2 .... part2 Not sure what string functions I can use here so that I can easily upd...
We are developing an ASP.NET application and carrying out the database design too. Kindly let me know if you know any best reference document for SQL Server database design - best practices. ...
We are working on a C# application, we've been using Linq to SQL or standard ADO (when performance needed) to work with SQL Server. We have a table layed out like so: Customer ID, Year/Month, Product Name, Quantity Each customer has additional columns per product. We need display this information in a data grid like so: Customer, Y...
Hello everyone, I am using SQL Server 2008 Enterprise. I need to transfer the database (as a whole) to another server (to make a duplicate database to setup another test environment). I have two choices, (1) making a full backup at source server/restore at destination server; (2) making detach at source server/attach at destination ser...
I have an application which has two database servers: one is the development database server and the other one is a production database server. Accidentaly, a couple of months back I published the application for use and it was pointing towards the development database. Since it was pointing to the development database, all the informa...
I'm SELECTing some aggregate data and grouping on the date and a particular field. I want to display all values in that field and a count for those values even if there was no data matching that field on that day. E.g. Date MyField Count 2009-09-25 A 2 2009-09-25 B 0 2009-09-24 A 1 2009-09-24 B ...
I need to create a sales & commission report Basically it goes (please forgive the blatent craziness of the SaleDate table, but I'm simplifying the business logic, and in reality it actually makes sense to have it this way) SELECT agentName, SUM(sales.Amount) AS Gross, SUM(sales.Amount * sales.Commission) AS Commission F...
I'm curious about at what extent I should be writing sproc's. Obviously for actions that require transactions, etc. However for a simple validation against one table and values within, is it still recommended to use a sproc rather than doing the SubSonic query in the code-behind? In one respect it does make sense to write the sproc, a...
Consider the need to create a resultset of dates. We've got a start and end dates, and we'd like to generate a list of dates in between. DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (@Date datetime) SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009' --need to fill @AllDates. Trying to avoid loop her...
So I have an interesting problem that's been the fruit of lots of good discussion in my group at work. We have some scientific software producing SQLlite files, and this software is basically a black box. We don't control its table designs, formats, etc. It's entirely conceivable that this black box's output could change, and our design...
How can i get this result with changing query below. foo 01 02 03 declare @a as int set @a = 1 select single.* from (select case when 0=0 then '0'+ case when @a = 1 then '1' when @a = 2 then '2' when @a = 3 then '3' end end as foo) single cross join (select 1 as bir union all select 2 union all select 3) multi ...
Hi, How do I Select records between two dates in two columns? Select * From MyTable Where 2009-09-25 is between ColumnDateFrom to ColumnDateTo I have a date (2009-09-25) and I like to select the records that is in the timeframe ColumnDateFrom to ColumnDateTo. Sample Record 1 ColumnDateFrom = 2009-08-01 AND ColumnDateTo = 2009-...
Say I write the query: INSERT INTO DestinationTable (ColumnA, ColumnB, ColumnC, etc.) SELECT FROM SourceTable (ColumnA, ColumnB, ColumnC, etc.) And my source table has 22 million rows. SQL server fills up my hard drive, and errors out. Why can't SQL server handle my query? Should I use a cursor and insert a row at a time? PS - it ...