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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Request Help Please

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 tt
GO
Go to Top of Page
   

- Advertisement -