A similar question has been asked, but since it always depends, I'm asking for my specific situation separately. I have a web-site page that shows some data that comes from a database, and to generate the data from that database I have to do some fairly complex multiple joins queries. The data is being updated once a day (nightly). I ...
I'm new to T-SQL command MERGE so I found a place in my SQL logic where I can use it and want to test it but can't figure out how exatcly should I use it: IF (EXISTS (SELECT 1 FROM commissions_history WHERE request = @requestID)) UPDATE commissions_history SET amount = @amount WHERE request = @requestID ELSE INSERT INTO ...
Im a bit new to T-SQL, Coming from a MySQL background Im still adapting to the different nuances in the syntax. Im looking to add a new column AFTER a specific one. I've found out that AFTER is a valid keyword but I don't think it's the right one for the job. ALTER TABLE [dbo].[InvStockStatus] ADD [Abbreviation] [nvarchar](32) DEFAULT ...
I am hoping this question fares a little better than the similar Create a table without columns. Yes, I am asking about something that will strike most as pointlessly academic. It is easy to produce a SELECT result with 0 rows (but with columns), e.g. SELECT a = 1 WHERE 1 = 0. Is it possible to produce a SELECT result with 0 columns (...
I have scripts for adding columns to tables which worked. When I run scripts to alter views with the new column the script fails because the columns are not recognized Msg 207, Level 16, State 1, Procedure UniqueTempDispositions, Line 76 Invalid column name 'servicerequestid'. Msg 207, Level 16, State 1, Procedure UniqueTempDisposition...
I have a number of stored procedures structured similarly to this: DECLARE @sql NVARCHAR(MAX) DECLARE @mdx NVARCHAR(MAX) CREATE table #result ( [col1] NVARCHAR(50), [col2] INT, [col3] INT ) SET @mdx = '{some dynamic MDX}' SET @sql = 'SELECT a.* FROM OpenQuery(LinkedAnalysisServer, ''' + @mdx + ''') AS a' INSERT INTO #result EXE...
Please see screenshot: Goal: id like to have comments nested 1 level deep The comments would be arranged so that rating of the parent is in descending order the rating of the children comments is irrelevant The left hand side of the screenshot shows the output that Id like. The RHS shows the table data. All of the comments are hel...
This code was once working on sql server 2005. Now isolated in a visual basic 6 sub routine using ADODB to connect to a sql server 2008 database it throws an error saying: "Login failed for user 'admin' " I have since verified the connection string does work if i replace the body of this sub with the alternative code below this sub. ...
The project I'm working on creates a local copy of the SQL Server database for each SVN branch you work on. We're running SQL Server 2008 Express with Advanced Services on our local machine to host it. When we create a new branch, the build script will create a new database with the ID of that branch, creates the schema objects, and cop...
I have a parameterized SQL query targetted for SQL2005 which is dynamically created in code, so I used the ADO.NET SqlParameter class to add sql parameters to SqlCommand. In the aforementioned SQL I select from a Table Valued Function with has defaults. I want my dynamic sql to sometimes specify a value for these default parameters, and...
Possible Duplicate: Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc. I have read the using "SELECT * FROM sometable" is bad practice, esp. if the query is being sent over a network. Specifically what is wrong with it? ...
I have a 'guests' table and 'invitations' table bound with many-to-one relationship in a postgreSQL database. I have removed some guests and now I want to remove invitations that have no guests. I tried using 'COUNT', but aggregates are not allowed in WHERE clause. ...
I need to select a top row for each category from a known set (somewhat similar to this question). The problem is, how to make this query efficient on the large number of rows. For example, let's create a table that stores temperature recording in several places. CREATE TABLE #t ( placeId int, ts datetime, temp int, PRI...
I butter-fingered a query in SQL Server 2000 and added a period in the middle of the table name: SELECT t.est.* FROM test Instead of: SELECT test.* FROM test And the query still executed perfectly. Even SELECT t.e.st.* FROM test executes without issue. I've tried the same query in SQL Server 2008 where the query fails (error: the ...
I am trying to concatenate a list of IDs into a varchar(max) to pass into an openquery for a bulk update of data. My question is, is there an easy way to see if a string is beyond the length a varchar(max) can handle aside from comparing it to the number? I have seen this: http://stackoverflow.com/questions/1761124/how-many-characters-...
I have a job interview coming that will focus on T-SQL and general SQL competency. Any suggestions on how to prepare for this? ...
So I have a set of data that includes a column of dates and an ID column. Here's a sample of the date data: 5/30/10 12:00 AM 5/30/10 12:01 AM 5/30/10 1:59 AM 5/30/10 1:59 AM 5/30/10 3:58 AM 5/30/10 3:58 AM 5/30/10 5:57 AM 5/30/10 6:57 AM 5/30/10 7:56 AM 5/30/10 7:56 AM 5/30/10 9:55 AM 5/30/10 11:54 AM What I'd like to do is create b...
I would like to update a record with two dates, leaving existing data intact if I do not have a new value to update with. Here is a sample table record: id last_foo last_bar -- ---------- ---------- 1 2010-05-30 2010-05-30 And the query I am using: UPDATE sampledates SET last_foo = @LastFoo, last_bar = @LastBar ...
Is there a way to capture STATISTICS IO and TIME within T-SQL for logging. ...
Hi All I have two stored procedures, the first one calls the second .. If I execute the second one alone it takes over 5 minutes to complete.. But when executed within the first one it takes little over 1 minute.. What is the reason ! Here is the first one ALTER procedure [dbo].[schRefreshPriceListItemGroups] as begin tran delete from...