how can i import data from 1 access table to 2 joined ms sql tables? 2

[aldi]

Hello all,

I have a task to import data from one access (2000) table to two ms sql (2005) tables.

The ID of TableA has to be inserted in a field of TableB so that the tables can be linked on those fields.

How can i do it using SQL Server Import and Export Wizard?

I know how to use the wizard, but have no idea on how to insert the data on two tables at same time, so that the id of tableA gets into a field of tableB

I've been searching for answers but only find how to import from one table to another.

Please help!!!!!!!!!!!

I'll appreciate any kind of help, hint or link to read.

Thanks in advance!!!

 

[gmmastros]

I don't think you can import in to two tables simultaneously. Instead, you should create another table that you import in to. After the data is imported in to a single table, then run a query that moves the data from the one table in to the separate tables.

This is a very common method for importing data. Not only because of the multiple table issue, but it also gives you an opportunity to clean up your data before it gets put in to the real tables.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian101]

Perhaps you could give some examples of what the 3 tables look like.

Simi

 

[aldi]

Thank you folks for your replies!

I imported the data letting the wiz to create a table for me, but I get a bunch of errors that I don't understand.
See below please.

Messages
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column "DATE" (90) on input "Destination Input" (71). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (71)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (71)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SEALDIES" (58) failed with error code 0xC0209029. 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. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

 

[aldi]

The source table has a date field and it is missing the date in 1,709 records, but that doesn't seems to be the problem because the wiz imported 1,941 out of 4,008 records with 1,703 nulls in the date field.

Any idea what is the problem?

 

[aldi]

I found a couple of bad entries in the date field. I fixed them to try again. I'll post back.

thanks!!!

 

[gmmastros]

I found a couple of bad entries in the date field.

That's exactly the point I was trying to make earlier. There always seems to be bad data. sigh.

By using a 'staging' table, you can set the data type to varchar, load the data, and then actively seek out bad data. In this case, you could select the data from the staging table where the date in the date column cannot be converted to a date. You could show all the data in that column. Prepare a report.... whatever.

I think it's kinda funny. Everyone thinks their data is perfect until they try to 'give it' to someone else. It's then that they see how ugly it really is.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aldi]

George,

After fixing the bad entries all the records were successfully imported into the temp table.

Is a stagging table the same as a temp table?

Anyways, I need help now on the second part. Moving some of the fields' data into one table and the others into another table, which will also needs the ID of the first table.

Example:

TableA
id
name
lastname

TableB
id
TableA id
address
phone

I hope this is clear enough!

Please bare with me....thanks

 

[gmmastros]

A staging table and a temp table are the same thing.

Can you show the structure of your input data? Also... the id's in table A and B... are they identity columns? Is there anything unique about the data???



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aldi]

Yes the IDs are identity columns (primary keys)

ToolID in Destination table One needs to go into OwnerID of Destination table Two.

Source Table:

CREATE TABLE [dbo].[SEALDIES](
[DATE] [datetime] NULL,
[NUMBER] [nvarchar](15) NOT NULL,
[TYPE] [nvarchar](6) NULL,
[LOC] [nvarchar](6) NULL,
[PRODUCT] [nvarchar](50) NULL,
[CUSTOMER] [nvarchar](20) NULL,
[MT] [nvarchar](2) NULL,
[UP] [nvarchar](2) NULL,
[SIZE1] [float] NULL,
[SIZE2] [float] NULL,
[COMMENTS] [nvarchar](46) NULL,
[Cell] [nvarchar](50) NULL,
[ModuleID] [smallint] NULL,
[ImageFileSpec] [nvarchar](50) NULL,
CONSTRAINT [PK_SEALDIES] PRIMARY KEY CLUSTERED

Destination Table One:

CREATE TABLE [dbo].[Tools](
[ToolID] [int] IDENTITY(1,1) NOT NULL,
[ToolNo] [varchar](150) NULL,
[Name] [varchar](250) NULL,
[Revision] [varchar](8) NULL,
[StatCodeID] [int] NULL,
[ImageFileSpec] [varchar](50) NULL,
[CADFileSpec] [varchar](150) NULL,
[LocationID] [int] NULL,
[Rack] [varchar](50) NULL,
[Bin] [varchar](50) NULL,
[Comment] [varchar](8000) NULL,
[UserCreated] [varchar](50) NULL,
[DateCreated] [datetime] NULL,
[UserModified] [varchar](50) NULL,
[DateModified] [datetime] NULL,
[VendorID] [int] NULL,
CONSTRAINT [PK_Tools] PRIMARY KEY CLUSTERED

Destination Table Two:

CREATE TABLE [dbo].[AdditionalInfo](
[AdditionalInfoID] [int] IDENTITY(1,1) NOT NULL,
[ModuleID] [int] NULL,
[OwnerID] [int] NULL,
[UserDefined1] [varchar](250) NULL,
[UserDefined2] [varchar](250) NULL,
[UserDefined3] [varchar](250) NULL,
[UserDefined4] [varchar](250) NULL,
[UserDefined5] [varchar](250) NULL,
[UserDefined6] [varchar](250) NULL,
[UserDefined7] [varchar](250) NULL,
[UserDefined8] [varchar](250) NULL,
[UserDefined9] [varchar](250) NULL,
[UserDefined10] [varchar](250) NULL,
[UserDefined11] [varchar](250) NULL,
[UserDefined12] [varchar](250) NULL,
[UserDefined13] [varchar](250) NULL,
[UserDefined14] [varchar](250) NULL,
[UserDefined15] [varchar](250) NULL,
[UserDefined16] [varchar](250) NULL,
[UserDefined17] [varchar](250) NULL,
[UserDefined18] [varchar](250) NULL,
[UserDefined19] [varchar](250) NULL,
[UserDefined20] [varchar](250) NULL,
[UserDefined21] [varchar](250) NULL,
[UserDefined22] [varchar](250) NULL,
[UserDefined23] [varchar](250) NULL,
[UserDefined24] [varchar](250) NULL,
[UserDefined25] [varchar](250) NULL,
[UserDefined26] [varchar](250) NULL,
[UserDefined27] [varchar](250) NULL,
[UserDefined28] [varchar](250) NULL,
[UserDefined29] [varchar](250) NULL,
[UserDefined30] [varchar](250) NULL,
[UserDefined31] [varchar](250) NULL,
[UserDefined32] [varchar](250) NULL,
[UserDefined33] [varchar](250) NULL,
[UserDefined34] [varchar](250) NULL,
[UserDefined35] [varchar](250) NULL,
[UserDefined36] [varchar](250) NULL,
[UserDefined37] [varchar](250) NULL,
[UserDefined38] [varchar](250) NULL,
[UserDefined39] [varchar](250) NULL,
[UserDefined40] [varchar](250) NULL,
CONSTRAINT [PK_AdditionalInfo] PRIMARY KEY CLUSTERED

 

[aldi]

Source Table Dest. TableOne

[DATE]
[NUMBER] [ToolNo]
[TYPE]
[LOC]
[PRODUCT] [Name]
[CUSTOMER]
[MT]
[UP]
[SIZE1]
[SIZE2]
[COMMENTS]
[Cell]
[ModuleID]
[ImageFileSpec] [ImageFileSpec]

All other fields go to Dest. table two including
ToolID from table one that goes into OwnerId of table two

I hope this help you to help me!

 

[gmmastros]

Generally speaking... this is not fun.

You won't know what the tool id is going to be until after you insert in to the table. So.... you'll need something unique in the input table so that you can join back to it later.

What I suggest is.... use a staging/temp table. In this table, create a uniqueidentifier column with a default of NewId(). Then, add a unique identifier column to table 1. When you insert the data in to this table, the toolid (identity column) will get created for you. Then, join back to the staging table on the unique identifier column so you can assign the tool id.

Let me show you what I am talking about by using an example.

Create Table Col1Data(Id Int Identity(1,1), Col1 Int, TempIdentifier UniqueIdentifier)
Create Table Col2Data(Col1Id Int, Col2 VarChar(20))

-- insert a couple records in to Col1Data
Insert Into Col1Data(Col1) Values(10)
Insert Into Col1Data(Col1) Values(20)

-- Create the staging table with a uniqueidentifier column
CREATE TABLE #Staging
	(
	Col1 int NULL,
	Col2 VarChar(20) NULL,
	TempIdentifier uniqueidentifier NULL
	)  ON [PRIMARY]

ALTER TABLE #Staging ADD CONSTRAINT
	DefaultUniqueIdentifier DEFAULT NewId() FOR TempIdentifier

-- put some data in the staging table for demonstration purposes.
Insert Into #Staging(Col1, Col2) Values(1, 'Blue')
Insert Into #Staging(Col1, Col2) Values(2, 'Red')

-- show what is in the staging table
Select * From #Staging


-- Insert in to the first table
Insert Into Col1Data(Col1, TempIdentifier)
Select Col1, TempIdentifier
From   #Staging

-- insert in to the second table, but 
-- join back to the first table to get 
-- the id number.
Insert Into Col2Data(Col1Id, Col2)
Select Col1Data.Id,
       #Staging.Col2
From   #Staging
       Inner Join Col1Data   
         On #Staging.TempIdentifier = Col1Data.TempIdentifier

-- show the data in the "real" tables
Select * From Col1Data
Select * From Col2Data

-- Clean up after ourselves
Drop Table Col1Data, Col2Data, #Staging

When you run the above code, notice how a GUID is created for each row in the staging table. This GUID is inserted in to table 1 so that we can join table 1 back to the staging table (giving us the value that was just inserted for the identity column).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aldi]

Thanks George, I'll give it a try.

I'll post back tomorrow.

 

[gmmastros]

good luck.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aldi]

Hi George,

DONE!!! Thank you very much for all your help!!!

I made the NUMBER column from the stagging table the primary key, then inserted the corresponding columns from the stagging table into TableB including the NUMBER field, although not needed there.

Then inserted the corresponding columns from the stagging table into TableA, including the NUMBER field, which is needed there.

And last run a query to update the OwnerID column from tableB with the ToolID column from TableA by joining the NUMBER columns from both tables.

And finally drop the stagging table.

 

[aldi]

I don't know what is wrong with the link to:
Thank gmmastros
for this valuable post!

I click on it and nothing happens.

I'll keep trying though

Thanks again George!!!

 

[gmmastros]

I don't know what is wrong with the link to:
Thank gmmastros
for this valuable post!

I appreciate the thought, and I'm really glad that you posted back that you finally got this working. Please do not worry about giving me a star.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aldi]

I was trying to give you the stars from the server and it seems it didn't like it.......but I did from my station.

Have a wonderfull weekend!!!

 

[Zygor]

I am trying to copy a range of records from an Acess database on a server to an SQL server table.

I have a wealth of experience as I've made 1 stored procedure already. I feel I may be close but could use a nudge. Here's what I have so far. It is a create procedure but I'll execute it after I create it.

CREATE PROCEDURE UpdateTable
@StartDate DATETIME,
@EndDate DATETIME
AS
INSERT INTO TABLE2 SELECT * FROM TABLE1
WHERE ActPatInDate BETWEEN @StartDate AND @EndDate;

I intend to pass the criteria when I execute it in VBA. TABLE2 is my sql server table. It is called 'Results'.

TABLE1 is a table called tbleCombined from my Access database on a totally different server, but one which I am mapped to.

Any help would be greatly appreciated. Thank you.

 

[Zygor]

doh! I meant to start a new thread. sorry