Desperately seeking SQL

[Cliphead]

Guys, been left in the lurch since the developer responsible has buggered off on holiday for five weeks. I need to get this done as soon as possible but I know very little about SQL.

Here's the scenario. Posgtresql database and one of the tables (shop_upgradecode), has this structure;

id integer
NOT NULL
nextval('shop_upgradecode_id_seq'::regclass)

product_id integer
NOT NULL

code character varying(500)
NOT NULL [uniq]

is_blacklisted boolean
NOT NULL

was_used boolean
NOT NULL

product_id will be 17

code is a list of 18,000 numbers like this 71938-47183-31335-71295-33203

is_blacklisted will be false

was_used will be false

What I have is a list of the code numbers that I need to get into the table but I haven't got a clue where to start. I would seriously appreciate any help to get this job done, kinda crucial to our operation.

[Weltchy]

I don't personally work with Posgtresql, but are the code numbers held in say a spreadsheet or something?

If so, I would look to manually import the data and then use something like

INSERT VALUES (ProductID,Code,Is_BlackListed,was_used)
SELECT 17,[Code],0,0 FROM ImportedCodeData

This assumes the import is a once off and you don't want to parameterise and put the sql into a stored proc or something?

Also, most sql databases stored boolean fields as BIT, hence the 0 for is_blacklisted and was_used. As I'm not familiar with Posgtresql, the data type of BOOLEAN might be valid and could take false instead

[Cliphead]

I don't personally work with Posgtresql, but are the code numbers held in say a spreadsheet or something?

If so, I would look to manually import the data and then use something like

INSERT VALUES (ProductID,Code,Is_BlackListed,was_used)
SELECT 17,[Code],0,0 FROM ImportedCodeData

This assumes the import is a once off and you don't want to parameterise and put the sql into a stored proc or something?

Also, most sql databases stored boolean fields as BIT, hence the 0 for is_blacklisted and was_used. As I'm not familiar with Posgtresql, the data type of BOOLEAN might be valid and could take false instead

I have a text file with all the code numbers in a list, it will be a one off import of that list and then left alone.

I'm assuming that

INSERT VALUES (ProductID,Code,Is_BlackListed,was_used)
SELECT 17,[Code],0,0 FROM ImportedCodeData <----------this is the text file?

[Weltchy]

I have a text file with all the code numbers in a list, it will be a one off import of that list and then left alone.

I'm assuming that

INSERT VALUES (ProductID,Code,Is_BlackListed,was_used)
SELECT 17,[Code],0,0 FROM ImportedCodeData <----------this is the text file?

The ImportedCodeData should be the resulting table from the CSV File import. Take a look at http://www.ensode.net/postgresql_csv_import.html which uses the copy command to transfer data from a CSV file into a temporarily created table.

I would try something like

create table ImportedCodeData(Code varchar(500));
copy ImportedCodeData from '/path/to/csv/codes.txt' DELIMITERS ',' CSV;

This should get the data into the ImportedCodeData table, after which you can insert the data using the original statement

As I said though, its all pure guesswork from me as I don't use POSTGRESQL!

[thunderlizard]

For a one-off like this I'd probably put the list of code numbers into Excel column A.
Then in cell B1, something like =CONCATENATE("insert into shop_upgradecode (ProductID,Code,Is_BlackListed,was_used) values (17, '", A1, "', false, false);")

then fill down column B.
Then copy the 1800 INSERT statements into your SQL editor and run them in. 18000 should give you just enough time to get a cup of tea while it runs.

(I've used Oracle syntax - postgresql might be different)

[Cliphead]

I got it! Outstanding help guys

[Sysman]

Sorry, missed this one earlier.

For future reference type this at this PSQL prompt:

\h copy

more generally:

'\h' gives help on SQL syntax
'\?' gives help on connecting to the database, changing directory etc.