May 27, 2015 at 4:53 pm
Hi,
I have two columns in an Excel spreadsheet: productid and colour.
These columns are populated with values, e.g. '123' and 'Red'.
I have about 200 rows and want to insert the colour values into rows in my SQL Server 2014 database where the productid matches.
What is the simplest way of doing this?
Thanks,
ma701ss
May 27, 2015 at 10:24 pm
ma701ss (5/27/2015)
Hi,I have two columns in an Excel spreadsheet: productid and colour.
These columns are populated with values, e.g. '123' and 'Red'.
I have about 200 rows and want to insert the colour values into rows in my SQL Server 2014 database where the productid matches.
What is the simplest way of doing this?
Thanks,
ma701ss
Quick suggestion, use the Import and Export Wizard, right click on the database in SSMS Object Explorer -> Tasks -> Import Data.
😎
May 28, 2015 at 7:03 am
Providing SQL Server has the correct permissions and access to the file you could install and use AccessDatabaseEngine to get data from the spreadsheet like this
OPENROWSET (
'Microsoft.ACE.OLEDB.12.0' ,
'Excel 12.0;HDR=NO;IMEX=1;Database=spreadsheet.xlsx',
'SELECT * FROM [Sheet1$]')
Far away is close at hand in the images of elsewhere.
Anon.
May 29, 2015 at 6:43 pm
Thanks tried the import wizard and it said 10 out of 11 rows successfully imported but no new rows appear to have been added. This method anyway seems to only import new rows but what I want to do is insert values into existing rows where the productid matches, so overwrite the existing values in the colour field. There doesn't seem to be an option for this.
June 5, 2015 at 8:35 am
Why not import the whole file into a new table and then update your existing table with an update statement.
Once it has worked you can drop the imported table if necessary.
June 10, 2015 at 1:05 pm
Will take the Other Side of this argument.
If you have a copy of MS Access, one of the options for creating a table is to either Import or Link Excel data.
Just import (or link) the Excel data into an Access Table.
Set up a table in SQL Server MyExcelImport
Use some simple code to create a DSN-LESS Linked table from Access to SQL Server using SQL Server Native Client 11.0
Using Access Query - append the Access table with the Excel data into the Access table linked to SQL Server.
Any table in SQL Server could be linked. The abilities to run append queries are possible.
June 10, 2015 at 2:48 pm
As noted above if you have MS Access use that, very easy to do what you want with MS Access.
However you can do this
write a delete statement to remove the whole row of data from the table. syntax is pretty straight forward
Delete From TBL1
where id ='123' or id='124' etc etc
then you can import your excel data as a whole new row.
A delete statement and Task > Import generally will be much quicker than trying to "update" only one field of a row.
June 15, 2015 at 11:21 am
Hi, thanks Nick's solution sounds the simplest way (to me) to get the data in, and I've now imported into a new table. However being new to SQL I don't know where to start with generating what I assume will be a fairly complicated query string.
I have a dbo.products and dbo.temp (where the two columns are stored). I need to update the rows I have in dbo.products with the data from dbo.temp, e.g.
For all rows in dbo.temp,
where productid in dbo.temp matches productid in dbo.products,
update column A in dbo.products, from column A in dbo.temp, and
update column B in dbo.products, from column B in dbo.temp
Would appreciate some help with this.
Many thanks
June 16, 2015 at 1:39 am
That looks like a standard update to me, something like this
UPDATEp
SETp.A = t.A,
p.B = t.B
FROMdbo.products p
JOIN dbo.temp t
ON t.productid = p.productid
If you need to add new rows as well then lookup INSERT on BOL (Books Online) or MERGE
Far away is close at hand in the images of elsewhere.
Anon.
June 17, 2015 at 3:18 am
Thanks, will give it a try.
June 18, 2015 at 3:47 am
Hello together,
not as a vendor, but with good experiences for users, who don't often need to Import Excel and other data sources, I recommend a product called flowheater. It's easy to use, can do many Actions, can be automated and is cheap (Version without Automation 69.- €). For Little amount of data, you can even use the Trial Version.
Best regards
June 22, 2015 at 6:37 pm
David's update query worked fine, thanks 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply