How to parse a bulk loaded table into individual fields in another table?

I am fairly new to SQL Server and the statement syntax

I have data that was bulk loaded into a SQL table 'SQNA_NameAddress_BulkLoad'  There is only 1 field defined in that table 'NABulkLoad nchar(130).

I now want to break apart the individual fields into table 'SQNA_NameAddress_Import'.  This is the definition of SQNA_NameAddress_Import
CREATE TABLE [dbo].[SQNA_NameAddress_Import](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ControlNumber] [int] NULL,
	[SequenceNumber] [int] NULL,
	[AddrLine1] [nvarchar](30) NULL,
	[AddrLine2] [nvarchar](30) NULL,
	[AddrLine3] [nvarchar](30) NULL,
	[AddrLine4] [nvarchar](30) NULL,
 CONSTRAINT [aaaaaSQNA_NameAddress_Import_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


IN the bulk record the fields contained are
Pos 1 - 7            ControlNumber
Pos 8 - 10          SequenceNumber
Pos 11 - 40        AddrLine1
Pos 41 - 70        AddrLine2
Pos 71 - 100      AddrLine3
Pos 101 - 130    AddrLine4

It would seem that I could do this with a insert statement that pulls pieces of the bulk import record and loads them directly into the 'Import' table but I'm not sure how to do that.

I think it would be something like this but I know the substring syntax isn't even close.

Insert Into SQNA_NameAddress_Import ( ControlNumber, SequenceNumber, AddrLine1,AddrLine2, AddrLine3, AddrLine4 )
                                                                         w.substring(NABulkLoad, 1,7) .............

From SQNA_NameAddress_BulkLoad w

Does anyone know the syntax that would work?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The "AS <columnname>" is of course optional, but I find it helps when adjusting the query later.

INSERT INTO [dbo].[SQNA_NameAddress_Import] (
    ControlNumber,
    SequenceNumber,
      [AddrLine1],
      [AddrLine2],
      [AddrLine3],
      [AddrLine4]
    )
SELECT
    SUBSTRING(NABulkLoad,  1, 7) AS ControlNumber,
    SUBSTRING(NABulkLoad,  8, 3) AS SequenceNumber,
    SUBSTRING(NABulkLoad, 11,30) AS AddrLine1,
    SUBSTRING(NABulkLoad, 41,30) AS AddrLine2,
    SUBSTRING(NABulkLoad, 71,30) AS AddrLine3,
    SUBSTRING(NABulkLoad,101,30) AS AddrLine4
FROM <bulk_table>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Perfect
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.