Geeks With Blogs
AngelEyes on .Net Tips, tricks and WTFs about Asp .Net, SQL Server and the likes. April 2007 Entries
Put multiple table data into one and add special data plus identity column - SQL Server
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 ......

Posted On Thursday, April 12, 2007 9:11 AM

SQL Server split function
This is used to "use list as a table", i.e. take a list of comma-separated values, and run queries on it.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 ......

Posted On Thursday, April 12, 2007 6:54 AM

Copyright © AngelEyes | Powered by: