I have a text file with Data in fixed width. The text file contains ID, Data,State. ID is just a INT. State has 2 CHAR statename. Data is the info of Companies that are registered in that State.
To put them in SQL server tables, I first dumped the text file to a SQLServer table called dbo.rawcompanyinfo_delimited
Note: for ease of explaining I have shown only 4 columns in Data column. It has 500 columns.
CREATE TABLE dbo.rawcompanyinfo_delimited(ID smallint NOT NULL, Data VARCHAR(MAX) NULL, State CHAR(2));
INSERT INTO dbo.rawcompanyinfo_delimited(100,'ABCINC 111 333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(200,'APPLE 213 333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(300,'BTEC 100 123.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(400,'S INC 123 333.0 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(500,'B INC 145 123.2 USD','PA')
I also have a mapping table that tells me Startingposition,length and Columnnames stored in map.CompaniesLenInfo
CREATE TABLE map.CompaniesLenInfo(Startingposition int not null, Length int not null, columnnames varchar(100) not null)
insert into map.CompaniesLenInfo(1,10,CompanyName)
insert into map.CompaniesLenInfo(11,3,CompanyID)
insert into map.CompaniesLenInfo(15,5,TotalIncome)
insert into map.CompaniesLenInfo(21,3,Currency)
I wrote a nested cursor that loops through map.CompaniesLenInfo and then through dbo.rawcompanyinfo_delimited and store the results in a table. like below
CREATE TABLE dbo.output(ID INT , CompanyName VARCHAR(10),CompanyID VARCHAR(3),TotalIncome VARCHAR(5),Currency VARCHAR(3)) ;
INSERT INTO dbo.output(100,'ABCINC','111','333.5','USD','PA')
INSERT INTO dbo.output(200,'APPLE','213','333.5', 'USD','PA')
INSERT INTO dbo.output(300,'BTEC' ,'100', '123.5', 'USD','PA')
INSERT INTO dbo.output(400,'S INC' ,'123', '333.0', 'USD','PA')
INSERT INTO dbo.output(500,'B INC' ,'145', '123.2', 'USD','PA')
DECLARE @ID INT,@StartPosition INT,@Len INT;
DECLARE @Data NVARCHAR(MAX), @ColumnName VARCHAR(100),@Val VARCHAR(MAX),@CompanyID CHAR(9),@State_Code VARCHAR(2);
DECLARE @Currency VARCHAR(10);
DECLARE @FinCursor AS CURSOR;
DECLARE @ParsingCursor AS CURSOR;
SET @FinCursor=CURSOR FAST_FORWARD FOR SELECT ID,Data
FROM map.CompaniesLenInfo WHERE State='PA';
OPEN @FinCursor;
FETCH NEXT FROM @FinCursor INTO @ID,@Data;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ParsingCursor = CURSOR FAST_FORWARD FOR SELECT StartPosition,Length,columnnames
FROM dbo.rawcompanyinfo_delimited;
OPEN @ParsingCursor;
FETCH NEXT FROM @ParsingCursor INTO @StartPosition, @Len, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Val = SUBSTRING(@Data,@StartPosition, @Len);
/* Not sure how to insert into dbo.output*/
FETCH NEXT FROM @ParsingCursor INTO @StartPosition, @Len, @ColumnName;
END
CLOSE @ParsingCursor;
DEALLOCATE @ParsingCursor;
FETCH NEXT FROM @FinCursor INTO @ID,@Data;
END
CLOSE @FinCursor;
DEALLOCATE @FinCursor;
How can I insert as a wide table.Can anybody suggest any other method that could be faster?
Here is a sample using PIVOT:
CREATE TABLE dbo.rawcompanyinfo_delimited(ID smallint NOT NULL, Data VARCHAR(MAX) NULL, State CHAR(2));
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(100,'ABCINC 111 333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(200,'APPLE 213 333.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(300,'BTEC 100 123.5 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(400,'S INC 123 333.0 USD','PA')
INSERT INTO dbo.rawcompanyinfo_delimited(id,data,State)values(500,'B INC 145 123.2 USD','PA')
CREATE TABLE CompaniesLenInfo(Startingposition int not null, Length int not null, columnnames varchar(100) not null)
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(1,10,'CompanyName')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(11,3,'CompanyID')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(15,5,'TotalIncome')
insert into CompaniesLenInfo(Startingposition,Length,columnnames)VALUES(21,3,'Currency')
SELECT *
FROM (
SELECT r.ID,r.State,SUBSTRING(r.Data,ci.Startingposition,ci.Length) AS val,ci.columnnames
FROM rawcompanyinfo_delimited AS r,CompaniesLenInfo AS ci
) AS t PIVOT(MAX(val) FOR columnnames IN (CompanyName,CompanyID,TotalIncome,Currency) ) p
+-----+-------+-------------+-----------+-------------+----------+ | ID | State | CompanyName | CompanyID | TotalIncome | Currency | +-----+-------+-------------+-----------+-------------+----------+ | 100 | PA | ABCINC | 111 | 333. | US | | 200 | PA | APPLE | 213 | 333. | US | | 300 | PA | BTEC | 100 | 123. | US | | 400 | PA | S INC | 123 | 333. | US | | 500 | PA | B INC | 145 | 123. | US | +-----+-------+-------------+-----------+-------------+----------+