Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
Populate a MS SQL Table from an Excel spreadsheet
I built a new table and now need to add data to it.
I appear to only have MS SQL Server Management Studio and a custom application which won't now anything about the new table.
How can I populate the new table with the data from the spreadsheet?
Microsoft SQL Server 2008
Microsoft SQL Server
8/22/2022 - Mon
If your data is not big you simply copy paste in management studio
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
You could set up a linked server to the Excel workbook.
Or you can use something like this:
INSERT INTO Staff.dbo.tblDetails
([StaffNo], [Field1], [Field2])
SELECT [Staff], [Field1], [Field2]
12.0', 'Data Source=C:\AccountNos.xls;E
xtended Properties=Excel 12.0')...[Details$]
This takes data from the 'Details' worksheet in the workbook AccountNos.xls and inserts it in tblDetails.
Obviously you'll need to adapt it for yourself.
barlet - Unfortunately I can't easily get the spreadsheet onto the server. I am logged in through Remote DeskTop
lludden - There is no IMPORT when I right click the table.
imnorie - Agin they are on different machines and I don't think I can link them that way.
lludden - your comment made me right click the table and there is an OPEN. I can then type in the data. That will work for now since I only need a few records to test the report I am writing.
I had tried every menu, screen, etc. I don't know why I didn't right click the table but I didn't. If I did I didn't see the OPEN option.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
Comment isn't the solution I used but it led me to the solution. The other comments are useful and normally would work but in my environment (logged in through RDP) I don't know how to implement them
Plans and Pricing
Certified Expert Program
© 1996-2022 Experts Exchange, LLC. All rights reserved. Covered by US Patent