Parse SQL data and load into two different tables "Help"

Transact-SQL
1

Hi All,

I couldn't figure it out how to parse the file and load into two SQL table. Source and destination are in SQL. Below are the sample SQL code for Source Sample table/data
and destination tables. Please let me know if my question is not clear. I really appreciated.
Here is my sample Source data

Source_id,sta_id ,Address1 ,City ,PaymentMethod,Invoice_Detail
21 ,KXV ,Rd , , ,
22 ,KXV ,PO Box234 ,Dallas, ,
31 , , , ,Cash ,;8555-4
42 ,Paid Sat , , , ,
Y ,180217 ,6 ,23550 , ,800-345-969
21 ,CNN ,TV , , ,
22 ,CNN ,PO Box 99 ,Chicag, ,
31 , , , ,Cash ,224;86799
42 ,Paid Sun , , , ,
Y ,180218 ,6 ,3456 , ,888-191-1900
Y ,180226 ,7 ,456 , ,800-900-8999

Here are my two Destination Tables that I want to load my source file

Table A
Destination_Header
id,Invoice#,Address1 ,City ,Sta_id
1 ,8555-4 ,PO Box234,Dallas,KXV
2 ,86799 ,PO Box 99,Chicag,CNN

Table B
Destination_Detail
id,Header_id,Month,Day,Year,Air,Phone#
1 ,1 ,02 ,17 ,18 ,6 ,800-345-969
2 ,2 ,02 ,18 ,18 ,6 ,888-191-1900
3 ,2 ,02 ,26 ,18 ,7 ,800-900-8999

----Source Table SQL******************
CREATE TABLE ##SourceTable
(
source_id VARCHAR(20)
,sta_id VARCHAR(20)
,Address1 VARCHAR(50)
,City VARCHAR(50)
,PaymentMethod VARCHAR(50)
,Invoice_Detail VARCHAR(50)
)

INSERT INTO ##SourceTable
SELECT '21','KXV','Rd',NULL,NULL,NULL
UNION
SELECT '22','KXV','PO Box234','Dallas',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash',';8555-4'
UNION
SELECT '42','Paid Sat',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180217','6','23550',NULL,'800-345-969'

INSERT INTO ##SourceTable
SELECT '21','CNN','TV',NULL,NULL,NULL
UNION
SELECT '22','CNN','PO Box 99','Chicago',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash','224;86799'
UNION
SELECT '42','Paid Sun',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180218','6','3456',NULL,'888-191-1900'
UNION
SELECT 'Y','180226','7','456',NULL,'800-900-8999'

SELECT * FROM ##SourceTable

----Destination Table A SQL******************
CREATE TABLE ##DestinationTable_A
(

id INT IDENTITY(1,1),
Invoice# VARCHAR(50),
Address1 VARCHAR(50),
City VARCHAR(50),
Sta_id VARCHAR(20)
)

----Destination Table B SQL******************
CREATE TABLE ##DestinationTable_B
(

id INT IDENTITY(1,1),
Header_id VARCHAR(50),
[Month] VARCHAR(50),
[Day] VARCHAR(50),
[Year] VARCHAR(20),
Air VARCHAR(20),
Phone# VARCHAR(20)

)

DROP TABLE ##SourceTable
DROP TABLE ##DestinationTable_A
DROP TABLE ##DestinationTable_B

2

Are you reading a CSV file? (looks like it). This kind of thing is easy to do with Integration Services. Otherwise, see this article CSV Splitter

3

Hi Gbritton,

Yes, I am using CSV File and Yes I am using SSIS. Can you please show me some type of example or guide me how can I do this thing in SSIS easily? That would be a big help if you can advise me.
In Above code, I forgot to add important field "Source_ID". I will update code. Basically, If I couldn't figure it out in SSIS, what I was thinking to loop through the batch and get the info what I need and load into table. (See above code Please)

Thank You for your time.

4

I was thinking if I use While Loop and then I will parse the field one by one then I can load into my Destination tables.

However, Can someone can help or guide me, How I can Accomplish my source data like this to add RANKID and then I can Pull One batch at a time and parse the field in my While Loop (See Below)

RankID,Source_id,sta_id ,Address1 ,City ,PaymentMethod,Invoice_Detail
1,21 ,KXV ,Rd , , ,
1,22 ,KXV ,PO Box234 ,Dallas, ,
1,31 , , , ,Cash ,;8555-4
1,42 ,Paid Sat , , , ,
1,Y ,180217 ,6 ,23550 , ,800-345-969
2,21 ,CNN ,TV , , ,
2,22 ,CNN ,PO Box 99 ,Chicag, ,
2,31 , , , ,Cash ,224;86799
2,42 ,Paid Sun , , , ,
2,Y ,180218 ,6 ,3456 , ,888-191-1900
2,Y ,180226 ,7 ,456 , ,800-900-8999

Blockquote

DECLARE @loopA_id INT ,
@loopA_times INT ,

@Address VARCHAR(50),
@CITY VARCHAR(20),
@iNVOICE VARCHAR(200),
@Sta_id VARCHAR(20);

SET @loopA_id = 1;
SET @loopA_times = 0;

-- Create Loop Tables
IF OBJECT_ID('tempdb..#ExactMatch') IS NOT NULL
BEGIN
DROP TABLE #ExactMatch;
END;

CREATE TABLE #ExactMatch
(
codA_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
sta_id VARCHAR(20),
);

-- Load Loop Table
INSERT INTO #ExactMatch
(
sta_id
)
SELECT DISTINCT
sta_id
FROM SourceTable_EIVA
WHERE source_id = '21'

-- Set Loop Times Variable
SET @loopA_times = @@rowcount;

WHILE @loopA_id <= @loopA_times
BEGIN

SELECT
@Address = e.Address1
,@CITY = e.City
,@iNVOICE = (
SELECT Invoice_Detail FROM SourceTable_EIVA e WHERE e.source_id = '31' --s.sta_id
--OR source_id = '31'
)
,@Sta_id = s.sta_id
FROM #ExactMatch s
INNER JOIN SourceTable_EIVA e ON e.sta_id = s.sta_id
WHERE codA_id = @loopA_id;

INSERT INTO DestinationTable_A_Eivav (Invoice#,Address1,City,Sta_id)
VALUES (@iNVOICE,@Address,@CITY,@Sta_id)

SET @loopA_id = @loopA_id + 1;
END;

5

Here's a good article on it https://blog.sqlauthority.com/2011/05/12/sql-server-import-csv-file-into-database-table-using-ssis/