Issue importing data to SQL table

Question

Post reply

Issue importing data to SQL table

September 12, 2023 at 5:32 am

This was removed by the editor as SPAM
September 12, 2023 at 5:35 am

This was removed by the editor as SPAM

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Jonathan AC Roberts wrote:

There is also JSON download in that link:

But, not the ID column the OP is looking for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Jonathan AC Roberts wrote:

If you just want an id you can just add ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) id, as a column to the query to get a sequential id column. e.g.

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/NACTA.Models' AS ns)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) id,
           T.C.value('(ns:CNIC)[1]', 'int') AS CNIC,
           T.C.value('(ns:District)[1]', 'varchar(50)') AS District,
           T.C.value('(ns:FatherName)[1]', 'varchar(50)') AS FatherName,
           T.C.value('(ns:Name)[1]', 'varchar(50)') AS Name,
           T.C.value('(ns:Province)[1]', 'varchar(50)') AS Province
      FROM @xml.nodes('/ns:ArrayOfHomeModel/ns:HomeModel') AS T(C);

If you look at the PDF, it's not just a "row" number. It's an actual ID and you'd need to guarantee that your import would be in the exact same order as the intended ID. I do that with other things because of the other fairly easy checks to do but there's no way to check the order in this case... except for the PDF. I don't know how to import those in T-SQL and normally go through an interim but manual step because it's not something that I've had to automate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

And, sorry... I was reading and answering from top down. You and the OP got all this together.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)