Have you ever had a list in excel, that you need to query from a database? Something like this:
abc
def
ghi
jkl
mno
Then you had to turn it into a SQL query like this:
Select * from mytable where some_value in (’abc’,'def’,'ghi’,'jkl’,'mno’)
Yeah…not so bad when its four or five…or you don’t work on a trading floor where a trader is down your neck wanting an answer immediately. Well I solved the problem. The following script will turn a comma/space/custom delimited list into a perfect string you can use in SQL.
If you’d like to run this on your own server or edit it. Contact me and I will gladly send you the source code.
http://www.gregjessup.com/scripts/SQL/
Greg SQL Server Stuff
I recently had to import a bunch of data from Excel into MS SQL Server. I kept getting failures, because I orignially tackled the problem by first designing and creating the table, then trying to import into my existing table. This was a pain in the neck. So what I decided to do was import the data and let it the table get automatically created. Then I edited the table to my liking truncated and re-imported. The step I took are found below.
Importing Excel data into SQL (if you are creating new tables.)1) Get your data ready in Excel, exactly how you want to see it, including an ID column if that is going to act as your unique identifier.
Example:
2) Next do an import using Microsoft SQL Server Managment Studio
3) When you get to this page…make sure the Destination table is named exactly like you will want it. This will save you from having to edit later
If you have an ID column that you want to auto increment…change it from
[ID] [INT] NOT NULL,
to
[ID] [int] IDENTITY(1,1) NOT NULL,
4) Now copy your finalized create statement.
5) Drop the original table you just created.
6) Run the edited Create Statement to recreate an empty table
7) Rerun the import but now point it specally to the empty table as the destination.
Greg SQL Server Stuff SQL Server Stuff
