CREATE TABLE BlogPosts ( PostID INT PRIMARY KEY not null, PostTitle NVARCHAR , BlogID int, TotalComments int ) May this query be simplified with any Join instead of correlated subquery? SELECT TOP 5 * FROM BlogPosts as t0 WHERE t0.PostID = (SELECT TOP 1 t1.PostID FROM BlogPosts as t1 WHERE t0.BlogID = t1.BlogID ORDER BY...
I have a SELECT * FROM #tmp and it returns 131 records. I wish to prepend this with a label so the result is format=transaction, followed by the rows each containing 131 columns. It is cumbersome to do the following; is there a nicer method? SELECT 'format=transaction', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,...
This posting is an update (with trigger code) from my earlier posting yesterday I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID Table Applicant is linked to table Location, table Company & table AppStatus. My issue is creating the joins in my query. When Applicant.AppStatusRow...
I've a SQL query that queries an enormous (as in, hundreds of views/tables with hard-to-read names like CMM-CPP-FAP-ADD) database that I don't need nor want to understand. The result of this query needs to be stored in a staging table to feed a report. I need to create the staging table, but with hundreds of views/tables to dig through...
I have a web-service that calls a stored procedure from a MS-SQL2005 DB. My Web-Service was timing out on a call to one of the stored procedures I have (this has been in production for a couple of months with no timeouts), so I tried running the query in Query Analyzer which also timed out. I decided to drop and recreate the stored pro...
I have a couple large tables (188m and 144m rows) I need to populate from views, but each view contains a few hundred million rows (pulling together pseudo-dimensionally modelled data into a flat form). The keys on each table are over 50 composite bytes of columns. If the data was in tables, I could always think about using sp_rename t...
Hello! I want to store a value that represents a percent in SQL server, what data type should be the prefered one? ...
I need to determine file type (i.e., MimeType) of stored data in the SQL Server 2008. Is there anyway, if possible using SQL query, to identify the content type or MimeType of the binary data stored in the image column? ...
I am getting a "SQL Server Error: arithmetic exception, numeric overflow, or string truncation" error here is the code below AQuery:= TSQLQuery.Create(nil); with AQuery do begin SQLConnection:- AConnection; SQL.Text:= 'Insert into.....'; ParamByName('...').asString:= 'PCT'; . . . try ExecSQL; finally AQuery.Free; end; end;...
This is a FACT Table in a Data Warehouse It has a composite index as follows ALTER TABLE [dbo].[Fact_Data] ADD CONSTRAINT [PK_Fact_Data] PRIMARY KEY CLUSTERED ( [Column1_VarChar_10] ASC, [Column2_VarChar_10] ASC, [Column3_Int] ASC, [Column4_Int] ASC, [Column5_VarChar_10] ASC, [Column6_VarChar_10] ASC, [C...
Like this question, except T-SQL instead of php. 206275947 = 2062759.47 etc. The problem I'm running into is that an attempt to SUM the values in this column is overflowing the integer datatype in SQL. SUM(CONVERT(money,[PaymentInCentsAmt])) Is just tacking on ".00" to the end of the value. What obvious thing am I missing? ...
I want to update a column in a table making a join on other table e.g.: begin tran update table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO = '110' But it is complaining : Msg 170, Level 15, State 1, Line 2 Line 2: ...
What value in the SqlDbType enumeration should I use for the numeric T-SQL data type? ...
I got this error running a query that goes against 2 tables with combined 50k rows. An error occurred while executing batch. Error message is: Insufficient memory to continue the execution of the program. How do I get around this? Edit I get this when I run DBCC MEMORYSTATUS Msg 2571, Level 14, State 1, Line 1 User 'XXXX\YYYY' doe...
Currently, I am calculating a finish date based on the start date (DateTime) and duration (# of days), but my calculations do not take into account weekends or holidays. So, my solution is not correct. This was just a starting point. I read some articles out there and one approach is to create a gigantic calendar table that has all the...
hi all, how can i using group by with union in t-sql? i want to group by the first column of a result of union, i wrote the following sql but it doesn't work. i just don't know how to reference the specified column (in this case is 1) of the union result. great thanks. SELECT * FROM ( SELECT a.id , a.time ...
Hi, I have a table having 2 columns EmployeeId (int) and EmployeeDetails(XMl type) EmployeeId EmployeeDetails 1 <Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee> 2 <Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age>...
I have a table delivery_history which is populated by a trigger: id(guid) order_number delivery_number haulier timestamp(getdate()) 1 1234 haulier1 2009-10-08 8:34:00 2 1235 haulier1 2009-10-09 9:15:00 1 1234 haulier2 2009-...
I have a table that looks something like the following : PropertyID Amount Type EndDate -------------------------------------------- 1 100 RENT null 1 50 WATER null 1 60 ELEC null 1 10 ...
I would like to create a SP or UDF where I supply a table and column name as a parameter and it does something to that target. I'm using Sql Server 2005 Trivial Example of what I'm trying to accomplish: CREATE FUNCTION Example (@TableName AS VARCHAR(100)) RETURNS TABLE AS BEGIN SELECT * INTO #temp FROM @TableName RET...