Combining multiple queries & returns

Question

I have a .csv file with a list of 100 product_ids. I have a SQL Server table which (amongst others) has a product_id column (4000+ ids) and a product_description column.

What I want to do is take the .CSV list of product_ids and run a query on the table to return a list of the relevant product_description.

So my simple query would be

select product_description 
from tablename 
where product-id = xxxxxxx.

But how do I supply the xxxxxx as a list (perhaps I just seperate with commas?) and dump the output into another csv.

So, you want to put the list of id's from your CSV into a query? — Radu Gheorghiu, Oct 23 '15 at 12:39
Please see ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles), where the consensus is "no, they should not"! — , Oct 23 '15 at 12:43
You can save your time with ms-office or even with openoffice itself. Short cut filter key (Alt+D+F+F). — Ragul, Oct 23 '15 at 12:46

Radu Gheorghiu · Answer 1 · 2015-10-23T12:47:14.803

0

Can't you just copy + paste the id's from the CSV into a query?

SELECT
    product_id
    , product_description
FROM <table>
WHERE product_id in (<<list of values from CSV>>).

Since they're already in a CSV, they should be comma delimited, so you can easily plug them into your query (if you open your file with a text editor).

Alternatively, you could do everything from SQL, like this:

CREATE TABLE #TempTable (
    ID integer
    , col2 ..
    , col3 ..
    etc. )
GO

BULK INSERT #TempTable
    FROM 'C:\..\file.csv'
    WITH
    (
    FIRSTROW = 2,           -- in case the first row contains headers (otherwise just remove this line)
    FIELDTERMINATOR = ',',  -- default CSV field delimiter
    ROWTERMINATOR = '\n',   
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )

And then just run:

SELECT
    product_id
    , product_description
FROM <table>
WHERE product_id in (SELECT ID FROM #TempTable)

If you want to export this result to another CSV then:

INSERT INTO OPENROWSET(
     'Microsoft.ACE.OLEDB.12.0'
    ,'Text;Database=D:\;HDR=YES;FMT=Delimited'
    ,'SELECT
          product_id
        , product_description
      FROM <table>
      WHERE product_id in (SELECT ID FROM #TempTable)' )

edited Oct 23 '15 at 12:47

answered Oct 23 '15 at 12:41

Radu Gheorghiu

20,049
16
72
107

Thanks Radu, I have created a temp table :create table #tmptable ( Product_id varchar(30), Product_desc varchar(100), Product_long_desc varchar(2000), ) – user2988641 Oct 23 '15 at 14:00
Then Bulk insert #tmptable from 'C:\Chris\portal.txt' with (FieldTerminator='\t', RowTerminator = '\n') go). But sql error message says Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. – user2988641 Oct 23 '15 at 14:05
Bulk insert #tmptable from 'C:\Chris\portal.txt' with (FieldTerminator=',', RowTerminator = '\n') go Still returns Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 1 (Product_id). – user2988641 Oct 23 '15 at 14:38
@user2988641 Try increasing the size of the Product_id column. Or change the datatype to nvarchar, in case the data for that column also has non-numeric characters. – Radu Gheorghiu Oct 23 '15 at 14:40
I have managed to create temp table which contain 3 rows. But when I run the following query it only retruns a single row. Why is this? SELECT product_id , product_desc FROM cm_products WHERE product_id in (SELECT product_id FROM #tmptable2) – user2988641 Oct 26 '15 at 09:19

Combining multiple queries & returns

1 Answers1