Import from excel to exsisting table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Import from excel to exsisting table

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-26 : 05:51:29

Hi,

I am trying to import data from an excel table into an existing table. Also there are more columns in the table than in the excel sheet.
I am not sure how to import to an existing table. Also during the import i have to add 9999 to the existing EmployeeID in the excel file.

Columns in the excel file:
EmployeeID
DepartmentName
UserName
FirstName
LastName
Email
WorkPhone
UserPassword
Active

Structure of the User_sys table in sql:
CREATE TABLE [dbo].[Users_sys](
[User_id] [bigint] NOT NULL,
[USer_name] [nvarchar](50) NULL,
[Given_Names] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Email] [nvarchar](50)NULL,
[Phone_no] [nvarchar](50) NULL,
[Password] [varchar](50)NULL,
[Role_code] [nvarchar](50)NULL,
[Candidate_id] [int] NULL CONSTRAINT [DF_Users_sys_Candidate_id] DEFAULT ((0)),
[Business_admin] [bit] NULL CONSTRAINT [DF_Users_sys_Business_admin] DEFAULT ((0)),
[Active] [bit] NULL CONSTRAINT [DF_Users_sys_Active] DEFAULT ((0)),
[NotActive_date] [datetime] NULL,
[Activation_date] [datetime] NULL,
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Users_sys_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Users_sys_User_Id_Upd] DEFAULT ((0)),
CONSTRAINT [PK_Users Table_sys] PRIMARY KEY CLUSTERED
(
[User_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Kristen
Test

22859 Posts

Posted - 2007-07-26 : 06:28:44

In the DTS Import there is (well, at least in SQL 2000 there was!) a button to press which lets you see all the columns in the table, and set up which Import field goes to which Table field.

You *can* set up some further processing to, for example, add 9999 - but I personally would add an extra, calculated, column to the XLS instead, and then stuff that into the ID field in the table.

(If this is an on-gong need, rather than one-off, then I suppose the Processing route would be better)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources