sql-server-2005 - Read For Learn

Add a column with a default value to an existing table in SQL Server

Syntax: Example: Notes: Optional Constraint Name:If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6 Optional With-Values Statement:The WITH VALUES is only needed when your Column is Nullable and you want the Default Value used for Existing Records.If your Column is NOT NULL, then it will automatically use the Default … Read more

SQL Server String or binary data would be truncated

You will need to post the table definitions for the source and destination tables for us to figure out where the issue is but the bottom line is that one of your columns in the source table is bigger than your destination columns. It could be that you are changing formats in a way you were … Read more

How to upsert (update or insert) in SQL Server 2005

Try to check for existence: You could easily wrap this into a stored procedure and just call that stored procedure from the outside (e.g. from a programming language like C# or whatever you’re using). Update: either you can just write this entire statement in one long string (doable – but not really very useful) – or … Read more

Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version. To check if a table exists use:

How to find sum of multiple columns in a table in SQL Server 2005?

Easy: or if you just want one row:

Sql Server string to date conversion

Try this and See CAST and CONVERT (Transact-SQL) for more details.

Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

You would mostly be using COUNT to summarize over a UID. Therefore COUNT([uid]) will produce the warning: Warning: Null value is eliminated by an aggregate or other SET operation. whilst being used with a left join, where the counted object does not exist. Using COUNT(*) in this case would also render incorrect results, as you would then be counting the … Read more

The multi-part identifier could not be bound

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly. The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the ‘comma’ joins, where the join condition is specified in the WHERE clause). Here’s an outline of … Read more

Best way to do nested case statement logic in SQL Server

You could try some sort of COALESCE trick, eg: SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc… )

Column name or number of supplied values does not match table definition

They don’t have the same structure… I can guarantee they are different I know you’ve already created it… There is already an object named ‘tbltable1’ in the database What you may want is this (which also fixes your other issue):

+ More