March 1, 2018 at 10:48 pm
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
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.
----*********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
March 2, 2018 at 2:10 am
Please will you show us what you've already tried, because it's not clear where you're stuck?
The best advice is to change the design - there's no way you should be mixing those two data sets in one table. But if you're stuck with it, surely you just insert into Destination B all the rows where sta_id is Y, and into Destination A everything else?
John
March 2, 2018 at 8:44 am
Hi John,
Thanks for your time to reply.
In above code, I missed important source field "Source_ID" that will tell where the batch start and ends.
John, I was trying to accomplish through SSIS, because my source is .txt. I am not very good in SQL that's the reason I asked for help or show me the right path.
Thank You!
March 2, 2018 at 9:58 am
Sorry, but you've confused me. First you said the source is SQL, now you're saying it's .txt. But if it's .txt, just bring it into a staging table - as you already appear to have done - and manipulate it from there. If you're not very good in SQL then that's going to be a problem for you. I'm happy to help out, but I need you to tell me where you're stuck.
John
March 2, 2018 at 4:52 pm
Sorry If I confuse you. Yes, first I said my source is SQL and yes the original source is my .txt. I thought it is easy for me to load the data into STG and do SQL to parse that data. Anyway, Below is the "While Loop" that I am using to parse the data.
I couldn't figure it out how can I pull One Batch (One Bath start from source_id = '21' to Next source_id = '21') then I can parse the field one by one and load into my destination tables.
Jon, I am sure now I didn't confuse you more.
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;
March 2, 2018 at 5:01 pm
If I can add "RANKID" in my source then I can easily Pull my data through above While Loop. Here is source table as an example.
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
March 3, 2018 at 4:54 pm
Is someone can help me to write a code to add "RankID" column in my source file?
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
What I want after add/populate "RankID"
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
Thanks in advance.
March 4, 2018 at 7:10 pm
There is absolutely nothing in your source data to guarantee the order of the data to do any batching or grouping with. If this is actually starting out as a text file, please attach it along with a record layout and a target table and one of us might be able to show you how to pull this off.
We also need to know the meaning behind the various Source_IDs and how many of each a "batch" can possibly have.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply