Jeff,

basically I generated a work table with the basic information where the datasets begin and end and used direct insert into <table> select substring(fieldname,1,2) as recordident from basetable where recordid...

Jeff, yes it is the railway schedule data

Thanks to one and all for your replies

I have mange to re-code my script and able to import the 8.4 million rows into their respective tables in 6 - 7...

Yes, string manipulation is intensive and once I have optimised for a physical machine, possibly improve the record dissection this will end up on sql database in the cloud, so...

getting the data into the database isn't an issue its breaking out the the differing record types ...

However I have manged to re-code my procedure it will now load the...

Yes, I totally agree unfortunately that is not possible to have the format changed ...

Sometimes we have to work with what we are given ....

🙂

hi,

Thanks for that certainlytime saving pulling column names in and constructing the sql code 🙂

regards

Andrew

nothing wrong with substring but if you have a lot of records, its a lot of string manipulation just strikes me, if you have a string in one format if...

you have assumed that the spaces are delimeters and there are none its 80 characters long and the spaces are empty fields/columns

the data is from a fixed width data stream,...

'BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D                  P'

my initial cut and paste got squeezed

BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D P

and for that to be translated to this table structure

[recordidentity] [nchar](2) NOT NULL,

[transactiontype] [nchar](1) NULL,

[trainuid] [nvarchar](6) NULL,

[daterunsfrom] [nvarchar](6) NULL,

[daterunsto] [nvarchar](6) NULL,

[daysrun] [nvarchar](7) NULL,

[bankholiday running] [nchar](1)...

To clarify that you tweak the configuration of reporting services so that when user is using the report it will know what folders/reports the user can see, restricting the data...