December 3, 2008 at 11:42 am
Howyadoin,
I'm trying to transfer data from my Access 2000 db to my SQL Server 2005 Express db. As part of the migration, I had to change a lot of field and table names to eliminate spaces and pound signs and the like. How do I define which source fields are to be transferred to the destination fields?
For example, the field names in the Access db are as follows:
[Real Property Owners].Property_Owner
[Real Property Owners].Property_Reffered
[Real Property Owners].[Account #]
[Real Property Owners].[First Name]
[Real Property Owners].[Last Name]
[Real Property Owners].[Address 1]
[Real Property Owners].City
[Real Property Owners].State
[Real Property Owners].Zip
[Real Property Owners].[Home Phone]
[Real Property Owners].[POE Phone]
[Real Property Owners].[SS#]
[Real Property Owners].Assesed_Value
[Real Property Owners].WellsFargo_Report_Dt
and the new values are:
[Real_Property_Owners].Property_Owner
[Real_Property_Owners].Property_Referred
[Real_Property_Owners].Account_Num
[Real_Property_Owners].First_Name
[Real_Property_Owners].Last_Name
[Real_Property_Owners].Address_1
[Real_Property_Owners].City
[Real_Property_Owners].State
[Real_Property_Owners].Zip
[Real_Property_Owners].Home_Phone
[Real_Property_Owners].POE_Phone
[Real_Property_Owners].SS_Num
[Real_Property_Owners].Assessed_Value
[Real_Property_Owners].WellsFargo_Report_Dt
I need to do this transfer on a daily basis to make sure my SQL db is up-to-date with the Access db that is presently in use until I roll out SQL.
Any help would be greatly appreciated!
Thanks,
-Mark
December 3, 2008 at 12:07 pm
Refer to Books On Line (BOL) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/717bcc19-9f86-4dcf-82cd-bc65a18fac6a.htm
where you will read:
The INSERT statement adds one or more new rows to a table. In a simplified treatment, INSERT has the following form:
INSERT [INTO] table_or_view [(column_list)] data_values
The INSERT statement inserts data_values as one or more rows into the specified table or view. column_list is a list of column names, separated by commas, that can be used to specify the columns for which data is supplied. If column_list is not specified, all the columns in the table or view receive data.
When column_list does not specify all the columns in a table or view, either the default value, if a default is defined for the column, or NULL is inserted into any column that is not specified in the list. All columns that are not specified in the column list must either allow for null values or have a default value assigned.
Further information in the BOL reference:
By using a VALUES clause to specify the data values for one row. For example:
INSERT INTO MyTable (PriKey, Description)
VALUES (123, 'A description of part 123.')
OR
Using a SELECT statement:
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView
Many more ways of accomplishing what you want to do, the above is just a start.
December 7, 2008 at 11:19 am
Just in case your question isn't about the format of the INSERT statement, but rather about how to designate your columns in the ACCESS database...
If your question is how you can reference source columns whose names include #signs and spaces, SQL will honor column names enclosed in brackets.
INSERT INTO Real_property_owners (Property_Owner,Property_ReferredAccount_Num)
SELECT Property_Owner
,Property_Reffered -- (sic)
,[Account #]
FROM (etc, etc)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply