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 |
|
kishorefeb28
Starting Member
2 Posts |
 Posted - 2013-05-29 : 04:52:23
|
Hi Experts,
i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated by tab delimiter.
i need to import this file to SQLSERVER 2008.
while importing this text file into SQL server, the first column in text file has 10 characters string
i need to split this string into 4 separate strings of fixed lengths (like1st string -2char, 2nd string - 2char, 3rd string - 3char, 4th string -- remaining chars) and insert it into 4 different columns in sql server.
example i have a string in first column like INAPHYD00001 when i import this into sql server it should split like
IN ------to be inserted into Country column in sql server.
AP -----to be inserted into State column in sql server.
HYD ----to be inserted into City column in sql server.
00001 --to be inserted into LocalityID column in sql server.
i heard i can use Bulk Copy but i'm not sure how to use it and also how to prepare a format file for this requirement.
please help me in this regard.
Thanks
Kishore
kishorefeb28@icloud.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-29 : 04:55:13
|
If you want to include transformation logic like splitting strings etc you may be better of using tool like SSIS
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-29 : 04:56:13
|
For splitting part use expression based on SUBSTRING function inside derived column transformation to create required columns out of first column value
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
Impoting text file(without delimiter) into sql2008