Excel Spreadsheet -> SQL Server

I have an excel spreadsheet that I need to insert into two database tables.

Each row in the spreadsheet signals a new record, but there is a catch. Columns A to J need to go into one table, while columns K to O need to go into another table but there needs to be a link/key created between the two tables so there's a link.

Any ideas anyone?
PeterErhardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

imran_fastCommented:
Do it using a staging table first move all the data into one stagetable inside sql server and then use sql commands to move it to repective two tables.
0
rboyd56Commented:
In the Exce1 worksheet create a deined range from A to J and another one from K to 0.  The when you do the import you specify the range as the table name in the Excel worksheet.  

As far as the link goes..is there a common column in each of the ranges that you can link on. IN other words does each of the ranges have a column that has the same data as the other range. Without that I do not see how you can link the data and have it consistently correct.
0
PeterErhardAuthor Commented:
Thanks for your response. Re your first paragraph, how did I specify the range, when I go and query the spreadsheet?

Unfortnaltey, no there isn't a link between the two, they're just on the same row.
0
rboyd56Commented:
In the worksheet in Excel, select the range A to J (all columns and rows that you want imported). The go to Insert - Define - Name. Type in the name that you want ot use and click OK.

Then to import the worksheet:

Create the tables with the proper column definitions as the range in Excel, except make the first column in the tables an identity range.
Use the Import Wizard to import the Excel worksheets. Use a select query and use select * from <defined range>.
Pick the table to import into click the ellipse (...) and check enable identity insert

Finish the wiard and run the package
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.