AngelEyes on .Net

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 a
from
(
select  2001 as year ,* from a1
union
select  2002,* from a2
union
select  2003,* from a3
)  as t

select * from a

Credits due: look at the second post on http://searchvb.techtarget.com/tip/0,289483,sid8_gci932171,00.html

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
select * from fnSplit('1 22 333 444  5555 666', ' ')