Import TEXT to TABLE in MSSQL
By: Fernando
Use this code to import value in text into a table in MSSQL
CREATE PROCEDURE dbo.EachLineToTable --'String1,String2,String3,String4,MyNameIs,TestDriveFromBIGString,GOGOGO,Bye'
@Text TEXT
AS
DECLARE @StrLine VARCHAR(8000)
DECLARE @Size BIGINT
DECLARE @Start BIGINT
DECLARE @Separator VARCHAR(1)
DECLARE @TempImport TABLE(Idx BIGINT IDENTITY(1,1), SplitedLine VARCHAR(8000), Size INT)
SET @Size = 1
SET @Start = 1
SET @Separator = ','
WHILE (@Start < DATALENGTH(@Text) + 1) BEGIN
SET @Size = CHARINDEX(@Separator, SUBSTRING(@Text, @Start, DATALENGTH(@Text)), 1)
IF @Size = 0 SET @Size = DATALENGTH(@Text) - @Start + 1
SET @StrLine = SUBSTRING(SUBSTRING(@Text, @Start, DATALENGTH(@Text)), 1, @Size)
SET @StrLine = REPLACE(@StrLine,@Separator,'')
INSERT INTO @TempImport(SplitedLine, Size) VALUES(@StrLine, LEN(@StrLine))
SET @Start = @Start + @Size
END
SELECT * FROM @TempImport
Archived Comments
1. Why you put this tutorial to JDBC section?
View Tutorial By: panoet at 2010-07-04 13:35:44
2. Why you put this tutorial to JDBC section?
View Tutorial By: panoet at 2010-07-04 13:35:23
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Calling a Stored Procedure from JDBC in Java
setSavepoint and releaseSavepoint Example in Java
PreparedStatement Example in Java
Creating Database Tables Using ANT
Using the DriverManager Class vs Using a DataSource Object for a connection
Stored Procedures example in SQL