SQL Statment - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Statment

Author

Topic

jfkennedy5
Starting Member

6 Posts

Posted - 2007-02-18 : 21:47:19

Hello All,

I imported a excel file from SSIS and created a table called Lockbox.
To avoid the user from having to change the excel file -it is being imported as is.
I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]

The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID.
The columns are in diffrent order.
And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF'
and convert to a diffrent data type [Site ID] to text.

Is their a sql statment that can do this?

SQL2005

Thanks for your time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-19 : 00:54:47

The basic idea when inserting is

INSERT INTO TargetTable (TargetColBlue, TargetCol1, TargetColG)
SELECT SourceColRed, SourceCol33, SourceColA FROM SourceTable

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-19 : 01:00:29

We handle this type of situation in the following way:

1. Create a Staging table which has format similar to the file being imported
2. Load all data in this table
3. Process the data to find out errors and show them to end user
4. Repeat the process unless there are no errors and data is in the format we need (by joining with certain mapping tables)
5. Load this data in the final table which can be verified and approved by the end-user.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources