Sql-server – Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

copyselectsql serversql-server-2005

I have a text file (or an xls) with a 35k lines, each containing a single ID.
I'm required to copy every entry of TableA to TableB where the ID is in said list.

Would it be best if I'd create a new table, with the IDs, in order to use this table TableC in the query as WHERE ID IN TableC? If so, whats the easiest way to fill the new table with the IDs from the txt file?

Or is there a way to use a text file or the list itself in the query?

I thought something like this query should be appropriate, but I have no clue how to use the text file.

SELECT * INTO TableB
FROM TableA
WHERE ID IN text_file

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

Best Answer

The easiest way I know of is creating a table in tempdb with a single column (id for instance), then expand the tempdb node in SSMS and find the table. Right click and select "Edit top 200 rows".

A grid will appear. Select the whole first row with the row header on the left, then paste the whole contents of the file.

BCP is an alternative way to load the file contents to the destination table. In this case I don't find it any way easier than copying and pasting the file contents in SSMS.

You could also use OPENROWSET or other techniques to reference the file directly in your query, but that requires copying the file to the server or messing with permissions: too much hassle for a one-off task.

Related Solutions

Sql-server – SQL Server 2008 R2 : multiple files and filegroup

1: yes.

2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.

3: Ah - what would that be good given that you are still reading one backup file ;)?

4: Not running the files on one underlying SAN and thus improoving your IO budget?

The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.

Sql-server – Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed

Another way to avoid temp tables would be this:

;WITH tmpTest AS
(
    SELECT  Num as [UserId]
            , Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
            , COUNT(*) OVER() AS Quant
    FROM dbo.StringToNumSet('2,3,1', ',') 
)
SELECT *
FROM tmpTest as T1
INNER JOIN 
    (
        SELECT Top 10 Id
            , Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
        FROM TableA WITH (NOLOCK)
    ) as T2 ON T1.RowNo = (T2.RowNo % Quant) + 1;

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 : multiple files and filegroup

Sql-server – Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed

Related Question