How to set a default value for an existing column
This will work in SQL Server: ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N’SANDNES’ FOR CityBorn;
tsql
SQL Server SELECT into existing table
SELECT … INTO … only works if the table specified in the INTO clause does not exist – otherwise, you have to use: INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key This assumes there’s only two columns in dbo.TABLETWO – you need to specify the columns otherwise: INSERT INTO dbo.TABLETWO (col1, … Read more
What is the use of GO in SQL Server Management Studio & Transact SQL?
It is a batch terminator, you can however change it to whatever you want
What is the meaning of the prefix N in T-SQL statements and when should I use it?
It’s declaring the string as nvarchar data type, rather than varchar You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, … Read more
Replace a newline in TSQL
Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this: SELECT REPLACE(REPLACE(@str, CHAR(13), ”), CHAR(10), ”)
how can I Update top 100 records in sql server
Note, the parentheses are required for UPDATE statements: update top (100) table1 set field1 = 1
SQL MAX of multiple columns?
Here is another nice solution for the Max functionality using T-SQL and SQL Server SELECT [Other Fields], (SELECT Max(v) FROM (VALUES (date1), (date2), (date3),…) AS value(v)) as [MaxDate] FROM [YourTableName] Values is the Table Value Constructor. “Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows … Read more
Select columns from result set of stored procedure
Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable. Declare @tablevar table(col1 col1Type,.. insert into @tablevar(col1,..) exec MyStoredProc ‘param1’, ‘param2’ SELECT col1, col2 FROM @tablevar
Is it possible to specify condition in Count()?
If you can’t just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values: select count(case Position when ‘Manager’ then 1 else null end) from … You can also use the sum aggregate in a similar way: select sum(case Position when ‘Manager’ then … Read more
Pad a string with leading zeros so it’s 3 characters long in SQL Server 2008
If the field is already a string, this will work SELECT RIGHT(‘000’+ISNULL(field,”),3) If you want nulls to show as ‘000’ It might be an integer — then you would want SELECT RIGHT(‘000’+CAST(field AS VARCHAR(3)),3) As required by the question this answer only works if the length <= 3, if you want something larger you need … Read more