Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Request Help Please
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrtweaver
Yak Posting Veteran

USA
67 Posts

Posted - 06/25/2015 :  20:05:08  Show Profile  Reply with Quote
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

India
29 Posts

Posted - 08/21/2015 :  02:24:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000