tsql

The different combinations of join are confusing, can someone boil it down a little?

Looking at JOIN's this weekend. I was reading up on Join and seeing a ton of combinations of JOIN, LEFT, RIGHT, OUTER, FULL, INNER. I checked the MSDN docs and it looks like the only allowed combinations are in the form: < join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ] [ < join_hint > ] JOIN so from tha...

charindex() counts whiteshars in the end, len() doesn't in T-SQL

I want to find an index of the last '/' character, but the problem is that LEFT(target, LEN(target) - CHARINDEX('/', REVERSE(target))) doesn't work because the string in target column has a lot of space characters in the end and the charindex function includes the spaces, but len doesn't. Is there any other function to replace one of the...

How can I speed up a T-SQL query

I've developed a couple of T-SQL stored procedures that iterate over a fair bit of data. The first one takes a couple of minutes to run over a year's worth of data which is fine for my purposes. The second one, which uses the same structure/algorithm, albeit over more data, takes two hours, which is unbearable. I'm using SQL-Server and...

t-sql replacing double quotes

I have a t-sql statement like Replace(field,'\''','\"') because i have two different results ''field1'' and "field2" but what if i consider those two different results the same and want to group them. I choose to group those two by replacing the first double quotes with the second style but although replaced they are not interpreted as ...

Occasional conversion error using SUM function

My app uses sql2000 and a select statement it uses will sometimes fail. Once a week or so the select returns the error 'Error Converting data type varchar to numeric' SQL: sum(case when ISNULL(form_prsn_id, -1) = irpd_prsn_id then convert(dec(11,2), case when valu_value = '' ...

Is it possible to make SP/Function as private ones?

I am using Microsoft SQL 2005 server. I have created several SP and Functions in TSQL. After more than one year's development, the number of SP/Functions have grown dramatically. Only a few of SP/Functions are used as scheduled jobs or triggers, and most of them are used internally(I mean they are called by SP/Functions in jobs and trigg...

Combining INSERT and UPDATE statement (SQL2005 Stored Procedure)

Hi, I need to extract records from a table, copy the data to a second table and then update the records in the first table to indicate that they have been copied across successfully. My current SP code is this: SELECT TBL_ADDRESSBOOKADDRESSES.* FROM TBL_ADDRESSBOOKADDRESSES INNER JOIN TBL_CAMPAIGNS ON TBL_ADDRESSBOOKADDRESSES.adds_AB...

Determine what user created objects in SQL Server

I'm trying to go through our development DB right now and clean up some of the old test procs/tables. Is it possible to determine what user created objects in a SQL Server 2005 database? If so, how would I go about finding that information? Edit: Just wanted to clarify that the objects in question already exist. Setting up auditing and ...

how to update value from another TSQL

I have to update value based on values from another table: update OracleOb..NS.myTable set name = (select name from myTable1 where id = 1) where id = 1 here the SQL has some problem. How can I get value from myTable1 and set it to myTable? I am using MS SQL 2005. Sorry I have to edit this question again. The table myTable is a li...

Remove the last character in a string in T-SQL?

How do I remove the last character in a string in T-SQL? I.E. 'TEST STRING' to return: 'TEST STRIN' thanks Dave ...

Preserve SQL Indexes While Altering Column Datatype

I have a smalldatetime column that I need to alter to be a datetime column. This is something that will be part of an install process, so it cannot be a manual procedure. Unfortunately, the column has a few indexes and a not null constraint on it. The indexes are performance related and would need to be retained only using the new data t...

How to select in result set UNION ALL operation

SELECT * FROM (SELECT BAR.DIAGNOSES FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.DIAGNOSES IS NOT NULL) UNION ALL SELECT BAR.UNDERLYINGCAUSE FROM FOO INNER JOIN BAR ON FOO.AN = BAR.AN WHERE FOO.ADMWARD IN (16,17) AND (BAR.UNDERLYINGCAUSE IS NOT NULL) UNION ALL SELECT BAR.UNDERLYINGCAUSE2 FROM FOO INNE...

Insert using single insert statement.

Hi, I want to insert the data in my table tblSubscriptions and I only want to use one insert statement. I am going to insert data for every userId in User Table. The following SQL doesnot work. Insert tblSubscriptions (UserID, ProductID, isACtive, SubscriptionDays, Price, MonthlyPrice, ProductType, CurrencyID) Values ((Select userID Fr...

How to Execute T-SQL from c#?

Can anybody give an example for executing a T-SQL statement using C#? ...

How to set morethan max size charecters in NVARCHAR(MAX),sql Server2005

Hi All, I am using declare @insertsql nvarchar(MAX) --above @insertsql for sp_executesql takes only nvarchar as input set @insertsql='--i am giving More than 10000 characters here -----' EXEC sp_executesql @insertsql, N'@inXMLRequest XML OUTPUT', @inXMLRequest OUTPUT how to insert morethan 10000 charecters in NVARCHAR(MAX) in sql...

Sorting SQL table

Hi, can anyone help me with T-SQL to sort this table ID Comment ParentId -- ------- -------- 3 t1 NULL 4 t2 NULL 5 t1_1 3 6 t2_1 4 7 t1_1_1 5 to look like this ID Comment ParentId -- ------- -------- 3 t1 NULL 5 t1_1 3 7 t1_1_1 5 4 t2 NULL 6 t2_1 4 Kind regar...

SQL Server: How to create a temp table with unkown columns dataype/names?

I need to make a query on multiple databases. I got the part for building the query for every database but now i need to put the result in something for each query then i can return it. @requete is a query ALTER PROCEDURE [dbo].[RequeteMultiBd] @requete varchar(max) AS BEGIN --get server + database name select dbo.trim(Ser...

SQL Error: The multi-part identifier "tableName.ColumnName" could not be bound.

When LEFT JOINing tables in a SQL query, sometimes I need to reference multiple tables in the ON clause. For example: SELECT p.Name, j.Job, s.Salary FROM PeopleTable p, JobTable j LEFT JOIN SalaryTable s ON s.PeopleID=p.PeopleID AND s.JobID=j.JobID However, the above would give this error: SQL Error: The multi-part identifier "p.Peop...

SQL Server: Why do these queries return different result sets ???

Query 1 = select top 5 i.item_id from ITEMS i Query 2 = select top 5 i.item_id, i.category_id from ITEMS i Even if I remove the top 5 clause they still return different rows. if I run "select top 5 i.* from ITEMS i" this returns a completely different result set !! ...

Three table join with joins other than INNER JOIN

I am learning SQL and am trying to learn JOINs this week. I have gotten to the level where I can do three table joins, similar to a lot of examples I've seen. I'm still trying to figure out the tiny details of how things work. All the examples I've seen of three table joins use INNER JOINS only. What about LEFT and RIGHT JOINs? Do ...