Say you want to combine the data from 3 tables - a1, a2, and a3. Now, you also want to add a special field, let's say it's the year, and it's 2001 for a1 2002 for a2 etc' In addition, you want the resluting table to have an identity column. What do you do? This is a real - life scenario which came up today, and here's the solution: select IDENTITY(int, 1,1) AS id_num , *into afrom (select 2001 as year ,* from a1union select 2002,* from a2union select 2003,* from a3) as t select * from a ...
Credits due: look at the second post on http://searchvb.techtarget.... CREATE FUNCTION dbo.fnSplit( @sInputList VARCHAR(8000) -- List of delimited items , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items) RETURNS @List TABLE (item VARCHAR(8000))BEGINDECLARE @sItem VARCHAR(8000)WHILE CHARINDEX(@sDelimiter,@sInp... <> 0 BEGIN SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInp...-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInp...+LEN(@sDelimiter),LEN(@sInputList))))...