load data into 3 separate tables using tsql .
-
Hi All,
I have below table. Basically, I dumped testddata as is to a table.
now i want the data to be separated.Little background.
Entrie data is 1 day worth data and assume that we get this data on daily basis as a flat file. Below is the explaination for flat file structure.
Always the 1st row will be header row of length 80. Infact all rows are of length 80.
next, i have a series of records from 1... 6. So, one set(1..6) is called 1 detailed txn information. Similarly, next consecutive 1,2...6 rows will be 2nd txn.
and then next 1...6 will be 3rd txn. this is fixed. all rows will have 80 char length.Next, we can see last 4 records called tail records.
Now, i want to segregate the entire table data into three separate tables. 1st record i.e. header row into 1 temp table. (i.e. all header info into one separate table)
all detail rows into 1 table. i.e. 1..6, 1..6 , 1..6 i.e. all 18 rows should be sent another temp table.
finally, the last 4 rows which we call it as tail records, must be stored in another temp table.How can I do that using TSQL ?
CREATE TABLE [dbo].[rawdata](
[c1] [varchar](max) NULL
)
GO--truncate table rawdata
-- header data
INSERT [dbo].[rawdata] ([c1]) VALUES (N'FUJI 170821170821192429 ')--detailed records
INSERT [dbo].[rawdata] ([c1]) VALUES (N'10000011210999908977580500856000252165108290727 4024647500030285 5411 6012')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'200000217081817595708191708214420000db0000000212956100000050200081800000002000 9')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30000030000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'400000417081904 ISS84051010151300C000000021295000000021295002000D000000000000')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'5000005D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 9')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'6000006000001168996 00000000000000000000000000021295 8')INSERT [dbo].[rawdata] ([c1]) VALUES (N'10000071210999908977580500856000252165108290727 9956983400030765 5411 6012')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'200000817081817595708191708214420000db0000000212956100000050200081800000002000 5')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30000090000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'400001017081904 ISS84051010151300C000000021295000000021295002000D000000000000')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'5000011D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 4')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'6000012000001168996 00000000000000000000000000021295 2')INSERT [dbo].[rawdata] ([c1]) VALUES (N'10000131210999908977580500856000252165108290727 7213791260030743 5411 6012')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'200001417081817595708191708214420000db0000000212956100000050200081800000002000 1')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30000150000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'400001617081904 ISS84051010151300C000000021295000000021295002000D000000000000')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'5000017D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 8')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'6000018000001168996 00000000000000000000000000021295 8')--Trailer records
INSERT [dbo].[rawdata] ([c1]) VALUES (N'100870500000539761140000001432002564361500000020705000000000000000002012912345')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'26008776000000000000000000000000006350000000000310195396000000000000000000004292')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30008777050000000000000000000000000000000000000000000000000000000000000000011111')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'40008778000000000000000000000000000000000000000000000000000000000000000000000022')SELECT SUBSTRING(c1, 1, 8), -- header is always varhcar dataype and position starts from 1 to 8
SUBSTRING(c1, 1, 1) as [Record Identifier], --detail + trail rec
SUBSTRING(c1, 2, 6) as [Record Number] --detail + trail rec
from [rawdata];Always the [Record Number] will be an ever increasing value... I think this might help.
Thanks,
Sam
-
You mention, in your post the "first" record/row. How do we determine row "1" from your data? (The answer is not the first row inserted).
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
This might work better as a preprocess step as this kind of logic generally lends itself better to scripting languages if you don't have an easy way to identify header and trailer records besides file position.
- Thom A - Wednesday, November 8, 2017 12:24 PM
Hi Thom,
I didnt get your question. Always the 1st row in the file is treated as header. Thats the way, we get it.from 2nd row, the actual detail records data gets started and is identified by a series od 1....6 record pattern. This is standard.
'FUJI 170821170821192429 ' is my 1st row in this example.not sure If I have answered your question correctly or not!
How about using cursor ? reading row by row?oh ok . may be below info can help or answer your question.
SELECT SUBSTRING(c1, 1, 8), -- header is always varhcar dataype and position starts from 1 to 8
SUBSTRING(c1, 1, 1) as [Record Identifier], --detail + trail rec
SUBSTRING(c1, 2, 6) as [Record Number] --detail + trail rec
from [rawdata];
And also , can we create a new temp table something like this , introducing a new identity column?
CREATE TABLE [dbo].[rawdata2](
id int identity(1,1),
[c2] [varchar](max) NULL
)
GOinsert into rawdata2(c2)
select c1 from rawdataAlso, assume that the "record identifier" is an increasing number then can we achieve our goal? may be in this example, i think , i duplicated the 1st set of detailed records. but in real file, the "record identifier" will be an ever increasing value.
So, doing can we accomplish dumping into 3 separate tables ?? note: the detail records i have shown only 3 i..e 1..6,1..6, 1..6 but in reality there could be hundreds of sets of detailed records. -
Thom's question was based on the fact that data in tables in SQL Server does not have any implicit ordering – the ordering of data in the file is not maintained in the SQL Server table.
If you want to pull data from a file and add a row number (or similar), you may find that SSIS is a better choice.If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Hi Thom and Phil,
Key is record number will be unique... its an ever increasing value... it my bad that i havent mentioned it.
Also, editing the original post test data . old data has duplicates for record identifier.truncate table rawdata
-- header data
INSERT [dbo].[rawdata] ([c1]) VALUES (N'FUJI 170821170821192429 ')--detailed records
INSERT [dbo].[rawdata] ([c1]) VALUES (N'10000011210999908977580500856000252165108290727 4024647500030285 5411 6012')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'200000217081817595708191708214420000db0000000212956100000050200081800000002000 9')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30000030000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'400000417081904 ISS84051010151300C000000021295000000021295002000D000000000000')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'5000005D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 9')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'6000006000001168996 00000000000000000000000000021295 8')INSERT [dbo].[rawdata] ([c1]) VALUES (N'10000071210999908977580500856000252165108290727 9956983400030765 5411 6012')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'200000817081817595708191708214420000db0000000212956100000050200081800000002000 5')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30000090000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'400001017081904 ISS84051010151300C000000021295000000021295002000D000000000000')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'5000011D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 4')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'6000012000001168996 00000000000000000000000000021295 2')INSERT [dbo].[rawdata] ([c1]) VALUES (N'10000131210999908977580500856000252165108290727 7213791260030743 5411 6012')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'200001417081817595708191708214420000db0000000212956100000050200081800000002000 1')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30000150000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'400001617081904 ISS84051010151300C000000021295000000021295002000D000000000000')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'5000017D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 8')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'6000018000001168996 00000000000000000000000000021295 8')--Trailer records
INSERT [dbo].[rawdata] ([c1]) VALUES (N'100870500000539761140000001432002564361500000020705000000000000000002012912345')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'26008776000000000000000000000000006350000000000310195396000000000000000000004292')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'30008777050000000000000000000000000000000000000000000000000000000000000000011111')
INSERT [dbo].[rawdata] ([c1]) VALUES (N'40008778000000000000000000000000000000000000000000000000000000000000000000000022')SELECT SUBSTRING(c1, 1, 8), -- header is always varhcar dataype and position starts from 1 to 8
SUBSTRING(c1, 1, 1) as [Record Identifier], --detail + trail rec
SUBSTRING(c1, 2, 6) as [Record Number] --detail + trail rec
from [rawdata]; -
I made a couple of assumptions that may not be accurate, but the following seems to work. The detail sections have six records where the first character ranges from 1-6; the tail records have a max of 4. Your table has an identity column.
I've created a field that determines which table the records belong to. From there, you should be able to load them into the appropriate tables.
CREATE TABLE #rawdata(
id INT IDENTITY,
[c1] [varchar](max) NULL
)INSERT #rawdata ([c1]) VALUES
(N'FUJI 170821170821192429 ')
, (N'10000011210999908977580500856000252165108290727 4024647500030285 5411 6012')
, (N'200000217081817595708191708214420000db0000000212956100000050200081800000002000 9')
, (N'30000030000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
, (N'400000417081904 ISS84051010151300C000000021295000000021295002000D000000000000')
, (N'5000005D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 9')
, (N'6000006000001168996 00000000000000000000000000021295 8')
, (N'10000011210999908977580500856000252165108290727 9956983400030765 5411 6012')
, (N'200000217081817595708191708214420000db0000000212956100000050200081800000002000 5')
, (N'30000030000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
, (N'400000417081904 ISS84051010151300C000000021295000000021295002000D000000000000')
, (N'5000005D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 4')
, (N'6000006000001168996 00000000000000000000000000021295 2')
, (N'10000011210999908977580500856000252165108290727 7213791260030743 5411 6012')
, (N'200000217081817595708191708214420000db0000000212956100000050200081800000002000 1')
, (N'30000030000GA BRAVOH WAG-CART duper CENTUSWM duperPIZZAS 0000063566USA')
, (N'400000417081904 ISS84051010151300C000000021295000000021295002000D000000000000')
, (N'5000005D0000000C00000000WM duperPIZZAS D000044 FUJIWLMT 8')
, (N'6000006000001168996 00000000000000000000000000021295 8')
, (N'10087750500000539761140000001432002564361500000020705000000000000000002012912345')
, (N'26008776000000000000000000000000006350000000000310195396000000000000000000004292')
, (N'30008777050000000000000000000000000000000000000000000000000000000000000000011111')
, (N'40008778000000000000000000000000000000000000000000000000000000000000000000000022')SELECT *,
CASE
WHEN ROW_NUMBER() OVER(ORDER BY id) = 1 THEN 'Header'
WHEN MAX(LEFT(c1, 1)) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) = 6 THEN 'Detail'
ELSE 'Tail'
END
FROM #rawdataDROP TABLE #rawdata
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, November 8, 2017 1:50 PM
Hi Drew ,
Thanks for the response. I have just modified the original data and forgot to mention that "record number" will be unique, may be that can help on solving the problem.
- vsamantha35 - Wednesday, November 8, 2017 12:52 PM
oh ok . may be below info can help or answer your question.
SELECT SUBSTRING(c1, 1, 8), -- header is always varhcar dataype and position starts from 1 to 8
SUBSTRING(c1, 1, 1) as [Record Identifier], --detail + trail rec
SUBSTRING(c1, 2, 6) as [Record Number] --detail + trail rec
from [rawdata];How does that SQL return the dataset up posted above? If we put that into a single statement:
DECLARE @YourString varchar(50) = 'FUJI 170821170821192429 'PRINT 'First value: ' + SUBSTRING(@Yourstring, 1, 8);PRINT 'Second Value: ' + SUBSTRING(@YourString, 1, 1);PRINT 'ThirdValue: ' + SUBSTRING(@Yourstring, 2, 6);We get the values:
First value: FUJI 170
Second Value: F
ThirdValue: UJI 17Why does your first column only have "FUJI" and your record Number only have "UJI". It should have " 070" on it too.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Always the first row will be a bunch of characters indicating a header and date in "YYMMDD" format , again some sort of file creation date in "YYMMDD" And a "HHMMSS".
- vsamantha35 - Wednesday, November 8, 2017 2:32 PM
Okay but even based on that the field you identified as record number is not unique?
Ah... okay so it seems to be unique and sequential up to the trailer records, then something weird happens.
SELECT ROW_NUMBER() OVER(ORDER BY CAST(SUBSTRING(c1, 2, 6) AS int) ASC), SUBSTRING(c1, 2, 6),c1 FROM #rawdata
WHERE
SUBSTRING(c1, 1, 1) IN('1', '2', '3', '4', '5', '6') - vsamantha35 - Wednesday, November 8, 2017 2:32 PM
I get the feeling the original poster thinks that we can use T-SQL to parse that first row differently than any of the rest, and that may or may not be realistic. I don't have time to mess with this one, but I think what the OP is doing is showing what kind of output they are looking for based on the original raw data file.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - vsamantha35 - Wednesday, November 8, 2017 2:32 PM
This doesn't explain the dataset I quoted. Could you explain?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply