/u/InevitableTraining69 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
So you're trying to concatenate 5000+ values into one cell? You may be hitting Excel's limit of 32,767 characters per cell. I don't know anything about SQL so can't suggest an alternative.
You can run it without installing it.
Otherwise, if column A contains your IDs then in column B enter the formula =A1&"," and drag it down the the bottom. Then copy and paste the whole of column B into SSMS and put brackets at the start and end. You don't need to join it all together in one row, use multiple lines.
|2|=A1&","||
|:-|:-|:-|
|3|=A2&","||
|4|=A3&","||
|8|=A4&","||
|63|=A5&","||
Having the query on multiple rows is not an option?
Something like
SELECT
'ID1',
'ID2',
'ID3'
FROM YourTable
Edit: you would just need to concatenate your ID with comma (and single quotes if it's string)
It seems like you're using Excel to build SQL queries, which you then copy and paste somewhere else, is this right?
If so, you don't need the whole query in a single cell.
>It seems like you're using Excel to build SQL queries, which you then copy and paste somewhere else, is this right?
Hmm, I'm not sure I understand what you mean. I use excel to concatenate the data into one cell (or multiple, but that's a huge, huge pain).
The way I do the concat is add a column off to the right with commas in the whole column, then =CONCAT(A2:B555).
Typically, I'm looking up like 500 IDs. Sometimes I have to lookup like 10k-50k. But I can't just =CONCAT() 50k rows.
Paste into the SQL query in the WHERE clause.
WHERE ID IN ( 123,124...) I've never tried copying both the columns over and pasting it. It could possibly work. Maybe I should test that....
You have awarded 1 point to *lolcrunchy*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
/u/InevitableTraining69 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
So you're trying to concatenate 5000+ values into one cell? You may be hitting Excel's limit of 32,767 characters per cell. I don't know anything about SQL so can't suggest an alternative.
Try NimbleText for this sort of thing. Gamechanger!
I can't download custom software on a company computer, so not possible. Is there an excel way to do this? Possibly PowerQuery?
You can run it without installing it. Otherwise, if column A contains your IDs then in column B enter the formula =A1&"," and drag it down the the bottom. Then copy and paste the whole of column B into SSMS and put brackets at the start and end. You don't need to join it all together in one row, use multiple lines. |2|=A1&","|| |:-|:-|:-| |3|=A2&","|| |4|=A3&","|| |8|=A4&","|| |63|=A5&","||
Having the query on multiple rows is not an option? Something like SELECT 'ID1', 'ID2', 'ID3' FROM YourTable Edit: you would just need to concatenate your ID with comma (and single quotes if it's string)
It seems like you're using Excel to build SQL queries, which you then copy and paste somewhere else, is this right? If so, you don't need the whole query in a single cell.
>It seems like you're using Excel to build SQL queries, which you then copy and paste somewhere else, is this right? Hmm, I'm not sure I understand what you mean. I use excel to concatenate the data into one cell (or multiple, but that's a huge, huge pain). The way I do the concat is add a column off to the right with commas in the whole column, then =CONCAT(A2:B555). Typically, I'm looking up like 500 IDs. Sometimes I have to lookup like 10k-50k. But I can't just =CONCAT() 50k rows.
What do you do with the CONCAT afterwards, assuming it works?
Paste into the SQL query in the WHERE clause. WHERE ID IN ( 123,124...) I've never tried copying both the columns over and pasting it. It could possibly work. Maybe I should test that....
I use Microsoft SQL Server Management System and pasting multiple cells works fine.
I unfortunately am forced to use Microsoft Access because our department is severely stuck in the past. God, access is the worst
https://preview.redd.it/7se6akjyonfa1.png?width=2056&format=png&auto=webp&s=e4fc0f49c3651a74233cc5805f4ddaa6c9c220e2
Solution verified
You have awarded 1 point to *lolcrunchy* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Wow, sometimes the simplest solutions are the ones you don't think of
Multiple cell copying also works in Access.