CODEWITHSUNDEEP
sql-servert-sqlincrement
Tofetopo
Tofetopo

Reputation: 496

SQL Server: How to generate a reference column

I'm relatively new on SQL and I have a doubt. I have a manufacturer table with fields ID, name, address, supplier_ref, etc. What I'm doing is scraping data from websites (manufacturers data), so after scraping I end up with a XML file with the scraped data i.e.:

<ID>2123</ID>
<name>Name</name>
<address>21 Tithebarn st</address> 
<supplier_ref>AX5145</supplier_ref>

The problem is that some of the manufacturers don't have a supplier_ref, so the supplier_ref field in the XML file generated comes up empty. I need to generate one and I don't know how or at what point. The supplier_ref is VARCHAR, so it cannot be auto-increment...

Any ideas? Thanks in advance.

Upvotes: 0

Views: 110

Answers (1)

eftpotrm
eftpotrm

Reputation: 2271

Without knowing what constitutes a valid supplier reference, it's difficult to say. Generating a varchar of the right length isn't tricky, it's generating one that'll pass any downstream validation and not cause clashes with legitimate values elsewhere in the data.

If I was dealing with ISBN codes for books (as the closest I've come to this before), there's a reserved space specifically for 'private ISBNs' (can't remember the actual terminology, sorry) for internal use. You can allocate them to whatever you want with confidence that they'll never arrive on the back of an actual book from a publisher.

That's what you need to find here. It could be that the 'XX' reference space is reserved, in which case you can create 'XX0001', 'XX0002' and so on in safety - from experience I'd do that using a counting table containing just an ID field which can then be combined with constants to give the output value you want, but there's a number of ways it can be done.

Find out how the supplier references actually work, and this isn't too tricky in code. The difficult bit is identifying the business rules.

Upvotes: 1

Related Questions