Hello expert,
Have built table in SQL Server Management Studio with create table statement
copied herewith below.
Now trying to get data into it from Excel spreadsheet.
So I Googled it and got some direction. The instructions say to open the Import Export Wizard
by going to the database icon in the tree (not the Table icon) and choosing Tasks>Import Data.
This gets me to the Wizard. It asks me about the Data Source and the path to the Excel Worksheet.
(I work for a healthcare company so we have old tools, SSMS ver 9 and SQL Server 2005. So
I and to convert the Excel from xlsx to xls because the SSMS ver 9 doesn't know that xlsx files
exist.)
Then it goes to Choose a Destination and this where the problem is. It allows a choice for
destination like Microsoft OLE DB Provider for SQL Server and SQL Native Client. I chose the former.
And the Server Name can be chosen and the Database. But it does not allow me to say which table in which to
write the data. It then asks me about the table to use in the table Export Worksheet$.
On the Complete the Wizard dialogue box it says it will copy the Worksheet to [TempData].['Export Worksheet$']
and the new table will be created.
A new table is not wanted. The data should be copied into the existing table.
Where does it let me choose the table?
Thanks.
Allen in Dallas
CREATE TABLE SFA.tempCrossInsurance
(
EMPLOYER_ID int,
EMPLOYER_NAME varchar(50),
SERV_PCKG_NAME varchar(50),
SERV_PCKG_ID int,
PAYER_PACKAGE_ID int,
PYR_PCKG_NAME varchar(50),
MARKET_ID int,
MARKET_NAME varchar(50),
PAYER_PACKAGE_ROLODEXID int
)
By accident I chose, as Data Source = SQL Native Client The Server NAme and the Database
then
Copy data from one or more tables or views
Then it loaded all the tables and I picked
SFA.tempCrossInsurance
Then Execute Immediately > Finish
But it says '0 rows transferred
This is because I transferred from the wrong source, the empty SFA.tempCrossInsurance table.
So I started over and picked as Data Source = Microsoft Excel
Oh. I just found it. As you said after I check the Export Worksheet box the existing
table becomes available under the Destination heading.
So I click thru to Finish. But alas no joy. I get in the Validating section
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - 'Export Worksheet$'" (1).
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (67).
(SQL Server Import and Export Wizard)
Is there a way around the validation error?
Thanks
Allen