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 |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2015-06-25 : 20:05:08
|
| I am looking for the proper command to do the following:I have a text file, layout is space delimited, the first number is 45 characters long, second number is 31 characters long, third number is 10 characters long, and fourth is just some ascii characters.I have created a table in SQL. The table has 5 columns all set as varchar.Now what i wish to do is insert in to column 1 data from the first number starting at position 10 and going through to position 16, insert into column 2 the data from second number, insert into column 3 data from first number starting at position 25 and going though to position 32, insert into column 4 the data from the fourth column, and insert into column 5 the data from positions 5 to position 7 of the first number.Hope this is clear.Thanks and have a great day |
|
|
Maithil
Starting Member
29 Posts |
Posted - 2015-08-21 : 02:24:06
|
| Hi,This Will Work./*Create One Table To Insert your Raw Data*/CREATE TABLE tmp_test1(Number1 varchar(max),Number2 varchar(max),Number3 varchar(max),Number4 varchar(max))GO/*Import Your Data Into Table "tmp_test1" from your Text File *//*Create Another Table Which Will give you Final Result*/CREATE TABLE tmp_test2(Column1 varchar(max),Column2 varchar(max),Column3 varchar(max),Column4 varchar(max),Column5 varchar(max))GO/*Now Insert Data into your Final Table.*/INSERT INTO tmp_test2(dbo.tmp_test2.Column1,dbo.tmp_test2.Column1,dbo.tmp_test2.Column3,dbo.tmp_test2.Column1,dbo.tmp_test2.Column5)SELECT SUBSTRING(tt.Number1,10,6), tt.Number2,SUBSTRING(tt.Number1,25,7),tt.Number4,SUBSTRING(tt.Number1,5,2)From dbo.tmp_test1 ttGO |
 |
|
|
|
|
|
|
|
Request Help Please