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?
 
Scott PletcherConnect With a Mentor Senior 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
 
mlcktmguyAuthor Commented:
Perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.