63
votes
6answers
128k views
Check if a row exists, otherwise insert
I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.
This is for a booking system, so it must be ...
37
votes
5answers
57k views
select statement to find duplicates on certain fields
can you help me with a sql statements to find duplicates on multiple fields
for example in psuedo code:
select count(field1,field2,field3) from table where the combination of field1, field2, field3 ...
14
votes
3answers
3k views
SQL Server: should I use information_schema tables over sys tables?
In SQL Server there is two schemas for metadata:
INFORMATION_SCHEMA
SYS
I have heard that INFORMATION_SCHEMA tables are based on ANSI standard. When developing e.g. stored procedures, should it ...
11
votes
3answers
341 views
How to delete a large record from SQL Server?
In a database for a forum I mistakenly set the body to nvarchar(MAX). Well, someone posted the Encyclopedia Britanica, of course. So now there is a forum topic that won't load because of this one ...
8
votes
5answers
14k views
nvarchar(max) still being truncated
So I'm writing a stored procedure in MS SQL Server 2008. It's a really long query and I have to write it dynamically, so I create a variable called @Query and make it of type NVARCHAR(MAX). Now, I ...
6
votes
5answers
12k views
SQL Comments on Create Table on SQL Server 2008
I need to create some pretty big tables in SQL Server 2008, while I do have SQL Server Management Studio, I would like to comment the tables and the columns when I create the table. How do I do this?
...
6
votes
2answers
4k views
SQL NVARCHAR and VARCHAR Limits
All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that ...
6
votes
1answer
191 views
Why doesn't comparison work in CONVERT?
Is it possible to use a comparison operator in a CONVERT or CAST function?
I've got a statement that looks like this:
SELECT
...
CASE field
WHEN 'Y' THEN 1 # If Y then True
...
5
votes
6answers
2k views
How to print VARCHAR(MAX) using Print Statement?
I have a code which is:
DECLARE @Script VARCHAR(MAX)
SELECT @Script = definition FROM manged.sys.all_sql_modules sq
where sq.object_id = (SELECT object_id from managed.sys.objects
Where type = 'P' ...
5
votes
5answers
2k views
How do I have a check constraint that refers to another table?
I have the following tables in a SQL Server 2008 db:
tblItem, which has an ItemID field;
tblGoodItem, which also has an ItemID field, and has a foreign key pointing to tblItem;
tblBadItem, which ...
3
votes
2answers
1k views
How can I assign inserted output value to a variable in sql server? [duplicate]
Possible Duplicate:
SQL Server Output Clause into a scalar variable
DECLARE @id int
INSERT INTO MyTable(name)
OUTPUT @id = Inserted.id
VALUES('XYZ')
I am trying like above. How is it ...
2
votes
2answers
37 views
SQL Server 2008. Are these 2 statements equal
DECLARE @IS_EMAIL CHAR(1);
IF @IS_EMAIL = '1' .....
IF (ISNULL(@IS_EMAIL , '0') = '1') .....
Will those 2 condition resolve to the same result for every value of @IS_EMAIL?
1
vote
2answers
27 views
dml query within WITH sql
I was just supposing that I could put a set query within WITH statement in SQL Server 2008.
So I wrote a few lines of code:
with temp as
(
SELECT row_number() OVER (ORDER BY ...
1
vote
1answer
560 views
Getting nth Element from last in a xml in Sql Server
Please consider this XML:
<Employees>
<Person>
<ID>1000</ID>
<Name>Nima</Name>
<LName>Agha</LName>
</Person>
...
1
vote
2answers
22 views
Query to list data based comparison of 2 x numeric values
I have a SQL Server 2008 R2 query:
select
comp_companyid, balt, clim
from
vCompanyOverCL
which returns multiple rows which each row as follows.
comp_companyid balt clim
-------------- ...
