CODEWITHSUNDEEP
sqlsql-server
stark
stark

Reputation: 2256

SQL Server : Query using data from a file

I need to run a query in SQL Server, where I have a particular number of values stored individually on separate lines in a text file, and I need to run a query in SQL server to check if a value in a column of the table matches any one of the value stored in the txt file.

How should I go about doing this ?

I am aware of how to formulate various types of queries in SQL Server, just not sure how to run a query that is dependent on a file for its query parameters.

EDIT :

Issue 1 : I am not doing this via a program since the query that I need to run traverses over 7 million datapoints which results in the program timing out before it can complete, hence the only alternative I have left is to run the query in SQL Server itself without worrying about the timeout.

Issue 2 : I do not have admin rights to the database that I am accessing which is why there is no way I could create a table, dump the file into it, then perform a query by joining those tables.

Thanks.

Upvotes: 0

Views: 1746

Answers (3)

Geetika
Geetika

Reputation: 9

  1. Bulk import the data from text file into a temporary table.
  2. Execute the query to do the comparison between your actual physical table & temporary table.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

There are lots of approaches.

Mine would be to import the file to a table, do the comparison with a regular SQL query, and then delete the file-data table if you don't need it anymore.

Upvotes: 1

DanielG
DanielG

Reputation: 1675

One option would be to use BULK INSERT and a temp table. Once in the temp table, you can parse the values. This is likely not the exact answer you need, but based on your experience, I'm sure you could tweak as needed.

Thanks...

SET NOCOUNT ON;

USE Your_DB;

GO

CREATE TABLE dbo.t (
    i int, 
    n varchar(10),
    d decimal(18,4),
    dt datetime
    );
GO


BULK INSERT dbo.t
    FROM 'D:\import\data.txt'
    WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Upvotes: 3

Related Questions