Copying columns to table with different design

I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design.I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data.Here are the two tables involved in the column copy:

Documents Table

DocID (Primary Key)

DocNo

SequenceNo

YearGiven

MonthGiven

DayGiven

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (empty – no data)

MonthGiven (empty – no data)

DayGiven (empty – no data)

After the copy column procedure I want the FullDocuments table structure to look like this:

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (full of transferred data)

MonthGiven (full of transferred data)

DayGiven (full of transferred data)

The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table.This is because each document has four types – the primary Text document and three supplemental documents (Background, Report, and Index).Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types).Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns.Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).

I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables).This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.

I tried exporting the data using the Export Wizard using the “Copy data from one or more tables” option.The transfer failed.The most relevant lines of the Error Report are:

·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: 0x80004005Description: "The statement has been terminated.".
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)

·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) 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)

I then tried the “Write a query to specify the data to transfer” option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck.Questions:

1.Is the query approach the best way to achieve my objective, or did I miss something in the “Copy data” option in the Export Wizard?

2.If the query approach is the best way, any suggestions on what the query will look like?

Thanks for any help you can provide.

It sounds as though you have a pretty complicated requirement here and therefore I would suggest you try to build a package using BIDS rather than using the import/export wizard.

-Jamie

|||Jamie: Thanks for the suggestion. It would never have occurred to me to use the Business Intelligence Development Studio for this purpose since I think of BIDS as a report generator instead of a tool for moving data around in a database. I'll have to look into this option.|||

BIDS is the tool for building Reporting Services reports, Analysis Services cubes, and Integration Services packages. Hence its a tool for building BI components - hence its name.

-Jamie