Avatar of Mike McCracken
Mike McCracken
 asked on

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?

mlmcc
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
barlet

If your data is not big you simply copy paste in management studio
ASKER CERTIFIED SOLUTION
lludden

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
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.
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
Norie

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]
    FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\AccountNos.xls;Extended 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.


Mike McCracken

ASKER
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.

Thanks

mlmcc
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
William Peck
Mike McCracken

ASKER
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

mlmcc