April 7, 2008 at 12:49 pm
hi guys i to import data ifrom my excel spreadsheet in my destop into my existing sql table. I tried using integration services but i kept getting an error since i have a pk and also a userid column that gets my userid from a function and a insertdate with the default getdate() column.
what would be the easiest way to do this?
April 7, 2008 at 1:41 pm
Is this something you'll need to do repeatedly, or is it a one-time situation?
If it's a one-time situation, using the Import Wizard from Management Studio should work.
If it's a repeatable situation, then you might look into importing into a "holding pen" type table, doing what you need to the data in there, then using a simple "insert ... select ..." type statement to move the data to the final table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 2:26 pm
hi i tried doing this and also integration services export/import wizard, i have done it in sql server 2000 but is not working for me now i get an error in those two columsn in my PK column, my userid (default function that gets userid), and insertdt column (default getdate), my spreadsheet only has two columns that aren't any of those two. I checked the enable insert checkbox. I keep getting this error message.
Copying to [DEV].[dbo].[tblDistributors] (Error)
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'ID_PK', table 'DEV.dbo.tblDistributors; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - tblDistributors" (23) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)
please help!
April 7, 2008 at 2:31 pm
That means that there is at least one row in the spreadsheet where ID_PK is blank. If you can't see on like that, likely it's at the bottom of the spreadsheet, after the rows with actual data. Excel sometimes thinks those are valid rows and tries to include them. You can fix that in your import by specifying that that column "is not null" in the import query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 2:54 pm
My id_pk column is an identity column so i didn't put it in my excel spreadsheet, is there something i am missing?. :crying:
April 7, 2008 at 2:59 pm
If you turn Identity Insert on, in the import, then you have to provide that data in the import. Otherwise, turn off Identity Insert in the import and make sure the column mapping has "ignore" for that column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 3:11 pm
thank you!:P
May 1, 2008 at 10:39 am
Thank you, I was struggling with a similar problem and just by selecting ignore from the Source column was able to import all my data. 🙂
September 12, 2008 at 7:18 am
Thanks All! This was helpful for me. I was thinking backwards about the "Enable Identity Insert"...that checking meant it was allowing the app to insert automatically, not that it allows the user to insert the identity...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply