I would use WHERE columnName LIKE ‘%[%]%’ SQL Server stores string summary statistics for use in estimating the number of rows that will match a LIKE clause. The cardinality estimates can be better and lead to a more appropriate plan when the square bracket syntax is used. The response to this Connect Item states We … Read more
In the case when the record already exists, you can store the matched id into a variable like this: DECLARE @MatchedId INTEGER; MERGE MyTable as t …. …. WHEN MATCHED THEN UPDATE SET @MatchedId = t.MyTableId; UPDATE: Here’s a full example. This demonstrates one way: DECLARE @UpdateVariable bit DECLARE @ChangeResult TABLE (ChangeType VARCHAR(10), Id INTEGER) … Read more
Use: ALTER TABLE dbo.mytable ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn For more info, see: Working with Default Constraints
Using ASCII(RIGHT(ProductAlternateKey, 1)) you can see that the right most character in row 2 is a Line Feed or Ascii Character 10. This can not be removed using the standard LTrim RTrim functions. You could however use (REPLACE(ProductAlternateKey, CHAR(10), ”) You may also want to account for carriage returns and tabs. These three (Line feeds, … Read more
Just adapt your current start of day code! All you want is start of month, start of hour. Its just the same… select dateadd(month, datediff(month, 0, getdate()),0) select dateadd(hour, datediff(hour, 0, getdate()),0)
In SQL: select dateadd(d,36464,’1899-12-30′) — or thanks to rcdmk select CAST(36464 – 2 as SmallDateTime) In SSIS, see here http://msdn.microsoft.com/en-us/library/ms141719.aspx
This is what I use /*==============================================================*/ /* Table: TABLE_1 */ /*==============================================================*/ create table TABLE_1 ( ID int identity, COLUMN_1 varchar(10) null, COLUMN_2 varchar(10) null, constraint PK_TABLE_1 primary key nonclustered (ID) ) go declare @CurrentUser sysname select @CurrentUser = user_name() execute sp_addextendedproperty ‘MS_Description’, ‘This is my table comment’, ‘user’, @CurrentUser, ‘table’, ‘TABLE_1’ go declare @CurrentUser sysname … Read more
Late entry that handles negative ISNUMERIC(zipcode + ‘.0e0’) –integer ISNUMERIC(zipcode + ‘e0’) –decimal For more see this
Try adding this line to the beginning of your stored procedure: SET FMTONLY OFF You can remove this after you have finished importing.
One other suggestion: Verify that the type was defined as a class, is not primitive, nested or generic PS: As far as “worked on MSSQL 2005 but not MSSQL 2008” – I quote Tom Petty: “You got lucky, babe!” 😉