Run query for each value in .csv file

Snappiedat · February 9, 2024, 5:46pm

Hi,

I have build a query with 1 declared variabele, a customer number.

This query gives a result with 1 row and 8 columns.

Is there a way to run this query for 15.000 customersnumbers out of a .csv file and put the result into 1 result.

robert_volk · February 10, 2024, 3:08am

CREATE TABLE #customers(CustomerNumber int not null PRIMARY KEY WITH (IGNORE_DUP_KEY=ON);
BULK INSERT #customers FROM 'C:\path_to_file\customers.csv' WITH (fieldterminator=',');

SELECT col1, col2 --- etc. put whatever columns in your query here
FROM myCustomerTable a
INNER JOIN #customers b ON a.CustomerNumber=b.CustomerNumber

Instead of iterating over each customer number and running a query, insert all customer numbers to a table and JOIN on that table to the original Customer table. This meets you goal of all customer numbers returned in a single result.

Snappiedat · February 10, 2024, 7:56am

Hi Robert,

Thanks for your reply. I'm not exactly sure how to use this with my query. But i dont know how to combine it with your code.

The query looks like this:

DECLARE @KLANT AS INT

SET @KLANT = 123456

-- FILL IN CUSTOMERNUMBER ABOVE

SELECT @Klant AS Klantnummer
, S.TotaalBetaaldVanSaldo
, S.MutatieSaldo
, S.SaldoVerzilverdePuntenInEURO
, S.TotaalSaldoGekochtePunten

FROM
(
SELECT

ISNULL((SELECT SUM(…

ISNULL(CAST( (SELECT MAX(..

ISNULL(CAST( (SELECT MAX(…

) S

Ive shortend it but the query does its job.

robert_volk · February 10, 2024, 11:15am

Can you post the entire query?

Snappiedat · February 10, 2024, 11:45am

DECLARE @KLANT AS INT

SET @KLANT = 124239

-- FILL IN CUSTOMERNUMBER ABOVE

SELECT  @Klant AS Klantnummer
		, S.TotaalBetaaldVanSaldo
		, S.MutatieSaldo
		, S.SaldoVerzilverdePuntenInEURO
		, S.TotaalSaldoGekochtePunten
		, S.TotaalSaldoOntvangenPunten
		, S.HuidigePuntenSaldo
		, CAST(S.SaldoVerzilverdePuntenInEuro / 50 * 2 AS DECIMAL(6,2)) OntvangenRente
		, S.HuidigeKaartSaldo
		, (s.SaldoVerzilverdePuntenInEURO + s.MutatieSaldo) - (S.TotaalBetaaldVanSaldo + S.HuidigeKaartSaldo)  AS Balans

FROM
(
SELECT 

ISNULL((SELECT SUM(BEDR_INGENOMEN) FROM dbo.BKLT33 WHERE KLANT = @KLANT),0) AS TotaalBetaaldVanSaldo,

ISNULL((SELECT SUM(MUTATIE) FROM dbo.BKLT35 WHERE KLANT = @KLANT),0) AS MutatieSaldo,

ISNULL(CAST( (SELECT MAX(PTS_KOOP)  FROM dbo.BKLT30 WHERE NUMMER = @KLANT ) / 480 * 50 AS DECIMAL(6,2)),0) AS SaldoVerzilverdePuntenInEuro,

ISNULL(CAST( (SELECT MAX(isnull(PTS_KOOP,0))  FROM dbo.BKLT30 WHERE NUMMER = @KLANT ) / 10.00 AS DECIMAL(6,2)),0) AS TotaalSaldoGekochtePunten,

ISNULL(CAST( (SELECT MAX(isnull(PTS_ONTVANGEN,0))  FROM dbo.BKLT30 WHERE NUMMER = @KLANT ) / 10.00 AS DECIMAL(6,2)),0) AS TotaalSaldoOntvangenPunten,

ISNULL((SELECT PTS_BALANS FROM dbo.BKLT31 WHERE NUMMER = @KLANT),0) AS HuidigePuntenSaldo,

ISNULL((SELECT SALDO_KAART FROM dbo.BKLT31 WHERE NUMMER = @KLANT),0) AS HuidigeKaartSaldo
) S

Snappiedat · February 10, 2024, 11:48am

Results in:

RogierPronk · February 10, 2024, 6:12pm

When you open the csv in Excel you can use =TEKST.SAMENV("SELECT "; A1; " UNION") or if you have the English version CONCAT("SELECT "; A1; " UNION") to create a complete statement. It would look like this:

DECLARE @KLANT AS TABLE
(
KLANT INT
)

INSERT INTO @KLANT
SELECT 124239 UNION
SELECT 124240

SELECT * FROM @Klant

-- FILL IN CUSTOMERNUMBER ABOVE

SELECT @Klant AS Klantnummer
, S.TotaalBetaaldVanSaldo
, S.MutatieSaldo
, S.SaldoVerzilverdePuntenInEURO
, S.TotaalSaldoGekochtePunten
, S.TotaalSaldoOntvangenPunten
, S.HuidigePuntenSaldo
, CAST(S.SaldoVerzilverdePuntenInEuro / 50 * 2 AS DECIMAL(6,2)) OntvangenRente
, S.HuidigeKaartSaldo
, (s.SaldoVerzilverdePuntenInEURO + s.MutatieSaldo) - (S.TotaalBetaaldVanSaldo + S.HuidigeKaartSaldo) AS Balans

FROM
(
SELECT
ISNULL((SELECT SUM(BEDR_INGENOMEN) FROM dbo.BKLT33 WHERE KLANT = Klant.KLANT),0) AS TotaalBetaaldVanSaldo,
ISNULL((SELECT SUM(MUTATIE) FROM dbo.BKLT35 WHERE KLANT = Klant.KLANT),0) AS MutatieSaldo,
ISNULL(CAST( (SELECT MAX(PTS_KOOP) FROM dbo.BKLT30 WHERE NUMMER = Klant.KLANT ) / 480 * 50 AS DECIMAL(6,2)),0) AS SaldoVerzilverdePuntenInEuro,
ISNULL(CAST( (SELECT MAX(isnull(PTS_KOOP,0)) FROM dbo.BKLT30 WHERE NUMMER = Klant.KLANT ) / 10.00 AS DECIMAL(6,2)),0) AS TotaalSaldoGekochtePunten,
ISNULL(CAST( (SELECT MAX(isnull(PTS_ONTVANGEN,0)) FROM dbo.BKLT30 WHERE NUMMER = Klant.KLANT ) / 10.00 AS DECIMAL(6,2)),0) AS TotaalSaldoOntvangenPunten,
ISNULL((SELECT PTS_BALANS FROM dbo.BKLT31 WHERE NUMMER = Klant.KLANT),0) AS HuidigePuntenSaldo,
ISNULL((SELECT SALDO_KAART FROM dbo.BKLT31 WHERE NUMMER = Klant.KLANT),0) AS HuidigeKaartSaldo
FROM @Klant Klant

Snappiedat · February 11, 2024, 7:19am

I've tried this but it doen't give a result. By the way it's not necessary to use a csv file. I could use/get the customernumbers from from an existing table (dbo.BKLT31.NUMMER).

The query should give the result: For each NUMMER in BKLT31 run the query and put it in 1 Result

RogierPronk · February 11, 2024, 9:19am

SELECT S.NUMMER AS Klantnummer
, S.TotaalBetaaldVanSaldo
, S.MutatieSaldo
, S.SaldoVerzilverdePuntenInEURO
, S.TotaalSaldoGekochtePunten
, S.TotaalSaldoOntvangenPunten
, S.HuidigePuntenSaldo
, CAST(S.SaldoVerzilverdePuntenInEuro / 50 * 2 AS DECIMAL(6,2)) OntvangenRente
, S.HuidigeKaartSaldo
, (s.SaldoVerzilverdePuntenInEURO + s.MutatieSaldo) - (S.TotaalBetaaldVanSaldo + S.HuidigeKaartSaldo) AS Balans
FROM
(
SELECT
Klant.Nummer,
ISNULL((SELECT SUM(BEDR_INGENOMEN) FROM dbo.BKLT33 WHERE KLANT = Klant.KLANT),0) AS TotaalBetaaldVanSaldo,
ISNULL((SELECT SUM(MUTATIE) FROM dbo.BKLT35 WHERE KLANT = Klant.KLANT),0) AS MutatieSaldo,
ISNULL(CAST( (SELECT MAX(PTS_KOOP) FROM dbo.BKLT30 WHERE NUMMER = Klant.KLANT ) / 480 * 50 AS DECIMAL(6,2)),0) AS SaldoVerzilverdePuntenInEuro,
ISNULL(CAST( (SELECT MAX(isnull(PTS_KOOP,0)) FROM dbo.BKLT30 WHERE NUMMER = Klant.KLANT ) / 10.00 AS DECIMAL(6,2)),0) AS TotaalSaldoGekochtePunten,
ISNULL(CAST( (SELECT MAX(isnull(PTS_ONTVANGEN,0)) FROM dbo.BKLT30 WHERE NUMMER = Klant.KLANT ) / 10.00 AS DECIMAL(6,2)),0) AS TotaalSaldoOntvangenPunten,
ISNULL((SELECT PTS_BALANS FROM dbo.BKLT31 WHERE NUMMER = Klant.KLANT),0) AS HuidigePuntenSaldo,
ISNULL((SELECT SALDO_KAART FROM dbo.BKLT31 WHERE NUMMER = Klant.KLANT),0) AS HuidigeKaartSaldo
FROM dbo.BKLT31.NUMMER Klant
) s

Snappiedat · February 11, 2024, 9:50am

no results

Is there a way to loop the complete query i posted and put the result in a separate table?

Or give a range of numbers for the declared variable and loop the query?

i would like to do 500 numbers at a time... That's where the idea came from for the .CSV file

RogierPronk · February 11, 2024, 3:49pm

There is no reason why you should loop, it should be avoided if possible. If you really want to you should take a look at the WHILE command.

WHILE (Transact-SQL) - SQL Server | Microsoft Learn