sql server QA

一、SPLIT LINE USE SQL:
I have a table like this
ID CommaSeperatedString
1 a,b,c,d,e
2 x,y,z
What I want is to convert this into
ID Text
1 a
1 b
1 c
2 x
2 y
like this.

ANSWER:
SELECT
T.id, RIGHT(LEFT(T.csv,Number-1),
CHARINDEX(',',REVERSE(LEFT(','+T.csv,Number-1))))
FROM
master..spt_values,
your_table T
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(T.csv)+1
AND (SUBSTRING(T.csv,Number,1) = ',' OR SUBSTRING(T.csv,Number,1) = '')

SOURCE:http://www.sql-server-performance.com/forum/threads/csv-to-columns.32141/#post-166346

 

二、update a table’s column inner join another table by a relative column

Update A set A.name=CSRName from B join (select ID, CSRName from B) as B on A.FKID=B.ID

 

三、 execute sql file using command in sql server 2008

osql -S"127.0.0.1" -U"sa" -P"sa" -d"Northwind" -i"%CD%\1.sql"

 

四、using SSCM(SQL SERVER MIGRATION ASSISTANT) TO migrate data from ORACLE TO SQL SERVER

Just click “Next”  refer to this artice:http://www.cnblogs.com/yinc/archive/2011/07/24/2115345.html, it is useful and convenient.