Importing sql data from one table into two tables

Importing sql data

[danielm28]

Hello,

Does anyone know how I go about importing data from 1 big table into 2 smaller tables that I created? Many of the column names that I created in the new tables aren't the same as they were in the old big table. I need to somehow transform data from the old columns and add them to their new prospective column(s).

Any help is greatly appreciated.

Here's the big table in my database:

CREATE TABLE [Nomination_Form] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EntryDate] [datetime] NULL ,
[CountryCode] [nvarchar] (1000) NULL ,
[SalonTypeCode] [nvarchar] (1000) NULL ,
[CustomerFirstName] [nvarchar] (4000) NULL ,
[CustomerLastName] [nvarchar] (4000) NULL ,
[CustomerCompany] [nvarchar] (4000) NULL ,
[CustomerAddress] [nvarchar] (4000) NULL ,
[CustomerCity] [nvarchar] (4000) NULL ,
[CustomerState] [nvarchar] (4000) NULL ,
[CustomerZip] [nvarchar] (4000) NULL ,
[CustomerCountry] [nvarchar] (4000) NULL ,
[CustomerPhone] [nvarchar] (4000) NULL ,
[CustomerFax] [nvarchar] (4000) NULL ,
[CustomerEmail] [nvarchar] (4000) NULL ,
[SalonFirstName] [nvarchar] (4000) NULL ,
[SalonLastName] [nvarchar] (4000) NULL ,
[SalonFirstName_2] [nvarchar] (4000) NULL ,
[SalonLastName_2] [nvarchar] (4000) NULL ,
[SalonName] [nvarchar] (4000) NULL ,
[SalonAddress] [nvarchar] (4000) NULL ,
[SalonCity] [nvarchar] (4000) NULL ,
[SalonState] [nvarchar] (4000) NULL ,
[SalonZip] [nvarchar] (4000) NULL ,
[SalonCountry] [nvarchar] (4000) NULL ,
[SalonFax] [nvarchar] (4000) NULL ,
[SalonPhone] [nvarchar] (4000) NULL ,
[SalonEmail] [nvarchar] (4000) NULL ,
[SalonType] [nvarchar] (4000) NULL ,
[SalonWebsiteAddress] [nvarchar] (4000) NULL ,
[CustomerAccepted] [nvarchar] (4000) NULL
) ON [PRIMARY]
GO

Here are the 2 smaller tables that I created:

CREATE TABLE [nominee] (
[nomineeID] [int] IDENTITY (1, 1) NOT NULL ,
[NominatorID] [int] NOT NULL ,
[fName] [nvarchar] (255) NOT NULL ,
[lName] [nvarchar] (255) NOT NULL ,
[salonName] [nvarchar] (255) NOT NULL ,
[address] [nvarchar] (500) NOT NULL ,
[city] [nvarchar] (255) NOT NULL ,
[state] [char] (2) NOT NULL ,
[province] [nvarchar] (500) NOT NULL ,
[zip] [nvarchar] (255) NOT NULL ,
[country] [nvarchar] (255) NOT NULL ,
[phone] [nvarchar] (50) NOT NULL ,
[fax] [nvarchar] (50) NULL ,
[email] [nvarchar] (255) NULL ,
[website] [nvarchar] (255) NULL ,
[type] [char] (1) NOT NULL ,
[accept] [bit] NOT NULL ,
[entryDate] [datetime] NOT NULL CONSTRAINT [DF_nominee_entryDate] DEFAULT (getdate())
) ON [PRIMARY]
GO

CREATE TABLE [nominator] (
[NominatorID] [int] IDENTITY (1, 1) NOT NULL ,
[fName] [nvarchar] (255) NOT NULL ,
[lName] [nvarchar] (255) NOT NULL ,
[companyName] [nvarchar] (255) NOT NULL ,
[address] [nvarchar] (500) NOT NULL ,
[city] [nvarchar] (255) NOT NULL ,
[state] [char] (2) NULL ,
[province] [nvarchar] (500) NULL ,
[zip] [nvarchar] (255) NOT NULL ,
[country] [nvarchar] (255) NOT NULL ,
[phone] [nvarchar] (50) NOT NULL ,
[fax] [nvarchar] (50) NULL ,
[email] [nvarchar] (255) NULL ,
[entryDate] [datetime] NOT NULL CONSTRAINT [DF_nominator_entryDate] DEFAULT (getdate())
) ON [PRIMARY]
GO

[WebJoe]

Are you looking for

INSERT INTO [nominee] ( 

[nomineeID], 

[NominatorID], 

[fName], 

[lName]... 

)

SELECT 

[CustomerID], 

[SalonTypeCode], 

[CustomerFirstName], 

[CustomerLastName]...

FROM [Nomination_Form] 

WHERE ...

Of course you have to do the mapping yourself

[danielm28]

yes, thank you. How do I go about the mapping process?

thanks

[duckhunter]

WebJoe's statement is the start. All you need to add are the CONVERT statements around your selected fields. In case you aren't following, you can provide the values to an insert statement with a select statement

INSERT INTO TABLE1 (FIELD1, FIELD2) SELECT VALUE1, VALUE2 FROM TABLE2 WHERE FIELD3=VALUE

Since your original fields are varchar(4000) and you are going down to varchar(255) in most cases and INT in a few, you need to convert the incoming data to the correct format.

WebJoe's statement with the CONVERTS:

INSERT INTO nominee (
nomineeID,
NominatorID,
fName,
lName)
SELECT
Convert(int,CustomerID),
Convert(int,SalonTypeCode),
Convert(varchar(255),CustomerFirstName),
Convert(varchar(255),CustomerLastName)
FROM Nomination_Form
WHERE ...

Realize that if you have data that is longer than 255 in those 4000 char fields, it gets chopped off. Also, if the data in the varchar(1000) fields going into INT fields is not a NUMBER, the INSERT will fail.

[WebJoe]

Hmm I didn't know about the convert-function,it makes sense to use it though, I just would have used other methods. Is that ANSI-SQL or a anufacturers flavor?

[duckhunter]

Believe that is SQL Server Specific. You can also use the CAST method which is fairly new.

Select CAST(Field1 AS char(255)) From .....

[danielm28]

Thanks WebJoe and DuckHunter.

I'm going to try your suggestions. I'll let you know how it goes.

[WebJoe]

thanks duckhunter, i keep that in mind just in case I run across a SQL Server mapping problem...