Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-05-09 : 16:02:50
|
Hi,I have one table called Depts with column DeptId,DeptDesc,OrgId,DeptLength .. This table is having dataI am using below query to get data from Depts table.select distinct DeptId, PARSENAME(DeptId,1),floor(DeptId) from Depts where (LEN(DeptId) - LEN( REPLACE(DeptId, '.', ''))) > 0 order by DeptIdI have anothertable called Departments its a empty table with columns DeptCode,Dept1,Dept2,DeptId. I need to fill this table with above table(Depts ) as shown below.where DeptCode -- auto generated column with Identity increment 1 Dept1 should fill with -- floor(DeptId) Dept2 should fill with -- PARSENAME(DeptId,1), DeptId should fill with -- DeptIdSo above Table 1 data should be copied in to Table2 as shown above.How can i do this?Thank you. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-09 : 16:13:33
|
You should be able to do a simple insert..selectINSERT INTO Departments (Dept1, Dept2, DeptId)SELECT DISTINCT DeptId , PARSENAME(DeptId, 1) , FLOOR(DeptId)FROM DeptsWHERE ( LEN(DeptId) - LEN(REPLACE(DeptId, '.', '')) ) > 0ORDER BY DeptId |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-05-09 : 16:23:56
|
ok. thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-10 : 00:29:34
|
quote: Originally posted by James K You should be able to do a simple insert..selectINSERT INTO Departments (Dept1, Dept2, DeptId)SELECT DISTINCT DeptId ,FLOOR(DeptId), PARSENAME(DeptId, 1) , FLOOR(DeptId)DeptIdFROM DeptsWHERE ( LEN(DeptId) - LEN(REPLACE(DeptId, '.', '')) ) > 0ORDER BY DeptId
shouldnt it be modified as above to be inline with below requirementDept1 should fill with -- floor(DeptId)Dept2 should fill with -- PARSENAME(DeptId,1),DeptId should fill with -- DeptId------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-10 : 01:36:34
|
The table that you're copying into - do you have the DDL. Watch out for any identity insrert issues. If this is a problem use: SET IDENTITY_INSERTJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|