January 24, 2012 at 10:07 pm
I am trying to import data from one table to another in SQL 2005 database. I imported the data into a new table from an Excel file. I also made sure the data types are the same on both tables. We have the following tables:
Parent table
[record_id] [int] NOT NULL, ( Auto Number)
[cmpy_code] [char](2) NOT NULL,
[par_code] [char](8) NOT NULL,
[surname] [char](30) NULL,
[pud12_code] [char](3) NULL, -Table referenced Field ( Linked to another table in other [pud23_text] [char](20) NULL, words it is avaialble from a drop down menu
[pud24_text] [char](20) NULL,
[pud25_text] [char](20) NULL,
I imported data from Excel into a table and called it upload
[par_code] [char](8) NULL,
[Bus] [char](3) NULL,
[Pickup_time] [char](20) NULL
I need to map fields like this
import [Bus] [char](3) NULL, into [pud12_code]
[import [Pickup_time] [char](20) NULL into [pud24_text]
I attempted to do this using the import wizard but when the wizards validates the data it fails the import proccess. I could not view the SQL because the Edit SQL button is grayed out when I edit the mapping.
I also tried the following query
INSERT INTO dbo.parent (pud12_code,pud24_text)
SELECT Bus, Pickup_time
FROM dbo.Upload
WHERE par_code.dbo.Parent = par_code.dbo.Upload
When I run this I get different messages:
Today I got
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.Upload'.
Yesterday there was a message about not Nulls not allowed in record_Id Also keeping in mind that the imported data will only affect some rows and not all rows withing the table parent.
I also created a table with the import data with the same strucure as the table to be imported into. Copied my data into that table and ran the import wizard but it fails the validation. My question is how to insert data into some columns of the table and some rows?. and how to do the same if we are inserting data into two tables or more. some simple examples will be agreat help 🙂
Your help and guidance are much appreciated.
January 24, 2012 at 10:44 pm
January 25, 2012 at 2:48 am
Thanks for the reply
umasingh I tried using the wizard but it fails every time. It keeps failing the validation and saying that record_Id not allowed to be null. something like this.
January 25, 2012 at 12:59 pm
I guess your problem is over here
[cmpy_code] [char](2) NOT NULL[\b]
[par_code] [char](8) NOT NULL[\b]
and thats why when you are trying to insert your records using
NSERT INTO dbo.parent (pud12_code,pud24_text)
SELECT Bus, Pickup_time
FROM dbo.Upload
WHERE par_code.dbo.Parent = par_code.dbo.Upload
you are getting an error message. If those columns do not allow nulls then maybe you should try something like
iNSERT INTO dbo.parent (pud12_code,pud24_text,cmpy_code,par_code )
SELECT Bus, Pickup_time, 'Def_val_1','Def_val_2'
FROM dbo.Upload
WHERE par_code.dbo.Parent = par_code.dbo.Upload
hope it helps..
January 26, 2012 at 1:01 pm
saghbash (1/24/2012)
I am trying to import data from one table to another in SQL 2005 database. I imported the data into a new table from an Excel file. I also made sure the data types are the same on both tables. We have the following tables:Parent table
[record_id] [int] NOT NULL, ( Auto Number)
[cmpy_code] [char](2) NOT NULL,
[par_code] [char](8) NOT NULL,
[surname] [char](30) NULL,
[pud12_code] [char](3) NULL, -Table referenced Field ( Linked to another table in other [pud23_text] [char](20) NULL, words it is avaialble from a drop down menu
[pud24_text] [char](20) NULL,
[pud25_text] [char](20) NULL,
I imported data from Excel into a table and called it upload
[par_code] [char](8) NULL,
[Bus] [char](3) NULL,
[Pickup_time] [char](20) NULL
I need to map fields like this
import [Bus] [char](3) NULL, into [pud12_code]
[import [Pickup_time] [char](20) NULL into [pud24_text]
I attempted to do this using the import wizard but when the wizards validates the data it fails the import proccess. I could not view the SQL because the Edit SQL button is grayed out when I edit the mapping.
I also tried the following query
INSERT INTO dbo.parent (pud12_code,pud24_text)
SELECT Bus, Pickup_time
FROM dbo.Upload
WHERE par_code.dbo.Parent = par_code.dbo.Upload
When I run this I get different messages:
Today I got
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.Upload'.
Yesterday there was a message about not Nulls not allowed in record_Id Also keeping in mind that the imported data will only affect some rows and not all rows withing the table parent.
I also created a table with the import data with the same strucure as the table to be imported into. Copied my data into that table and ran the import wizard but it fails the validation. My question is how to insert data into some columns of the table and some rows?. and how to do the same if we are inserting data into two tables or more. some simple examples will be agreat help 🙂
Your help and guidance are much appreciated.
Did you create the Upload table in the 'dbo' schema? A SQL Server database can be configured such that new objects are created in the user's schema by default, so if your CREATE TABLE statement didn't specify dbo.Upload as the name of the table, it might have been created in another schema, e.g. OurSQLServer\SQLuser.Upload. A query on dbo.Upload would then return the "Invalid object name" error.
Also, you seem to have the format of your column names wrong in the WHERE clause. See this from BOL:
Referencing Columns by Using Identifiers
Column names must be unique within a table or view. You can use up to three prefixes to specify columns in a query where more than one table that is referenced may have a column of the same name. Any one of the following formats is acceptable:
database_name.schema_name.object_name.column_name
database_name..object_name.column_name
schema_name.object_name.column_name
object_name.column_name
http://msdn.microsoft.com/en-us/library/ms187879.aspx
Look into using aliases for your table names to save you some keystrokes:
http://msdn.microsoft.com/en-us/library/ms187455.aspx
I don't see any data type compatibility issues in your INSERT statement. You could be having a problem with trying to insert a NULL in the pud12_code column - when you say it is a "Table - reference field", do you mean that this column is a foreign key to another table? If so, you would not be able to insert rows into your Parent table if the pud12_code column value does not appear in the other table.
Jason Wolfkill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply