We help IT Professionals succeed at work.

Conditional data insert

234 Views
Last Modified: 2010-08-05
I'm trying to insert data from one SQL 2000 table to another within the same database. I need to compare two tables and make sure they match prior to insertion. This is what I have right now but it throw an error

MSG 107 the column prefix table2 does not match with a table name or alias name used in the query

INSERT INTO table2 (column2)
SELECT replace(convert(varchar,date,1),'/','')
FROM table1
WHERE table2.column1 = table1.column1
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
INSERT INTO table2 (column2)
SELECT replace(convert(varchar,date,1),'/','')
FROM table1 INNER JOIN
    table2 ON table2.column1 = table1.column1
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try either of these:

INSERT INTO table2 (column2)
SELECT replace(convert(varchar,date,1),'/','')
FROM table1, Table2
WHERE table2.column1 = table1.column1

or....

INSERT INTO table2 (column2)
SELECT replace(convert(varchar,date,1),'/','')
FROM table1 Join Table2 ON table2.column1 = table1.column1

Commented:
Yes you can use inner join like mathew said,
you can also try
-----------
INSERT INTO table2 (column2)
SELECT replace(convert(varchar,date,1),'/','')
FROM table1, table2
WHERE table2.column1 = table1.column1

It's better to use inner join, in SQL Server 2005, seems only inner join works

Author

Commented:
these aren't working for me. I'm getting a null error. Basically I need to replace a value already in table2(column2) with data from the select statement where table2 column 1 and table 1 column 1 equal the same thing
CERTIFIED EXPERT
Top Expert 2010

Commented:
josgan said:
>>Basically I need to replace a value already in table2(column2) with data from the select statement
>>where table2 column 1 and table 1 column 1 equal the same thing

Then you want UPDATE, not INSERT.

UPDATE table2
SET column2 = table1.column_from_table1
FROM table2 INNER JOIN
    table1 ON table2.column1 = table1.column1

Author

Commented:
Ok, that seemed to work, how can i also change the data type from date/time?
 Like this
SELECT replace(convert(varchar,date,1),'/','')
CERTIFIED EXPERT
Top Expert 2010

Commented:
UPDATE table2
SET column2 = <put your expression here>
FROM table2 INNER JOIN
    table1 ON table2.column1 = table1.column1

Author

Commented:
UPDATE TABLE2
SET COLUMN2 = replace(convert(varchar,TABLE1.COLUMN1,1),'/','')
FROM TABLE2 INNER JOIN
TABLE1 ON TABLE2.COLUMN1 = TABLE1.COLUMN1

I tried this and i'm getting
syntax error converting datetime from character string.
Top Expert 2007

Commented:
UPDATE TABLE2
SET COLUMN2 = TABLE1.COLUMN1
FROM TABLE2 INNER JOIN
TABLE1 ON TABLE2.COLUMN1 = TABLE1.COLUMN1

Are Table1.Column1 and Table2.Column2 both  DATETIME types?
Top Expert 2007

Commented:
if that doesn't work for you, can you please also indicate the result of these queries?

select * from syscolumns where id = object_id( 'table1' ) and name in ('column1', 'column2')
select * from syscolumns where id = object_id( 'table2' ) and name in ('column1', 'column2')

Thanks.

Author

Commented:
they are not both datetime types

Author

Commented:
column 1
1134015171
175      0      175      16      0      0      2      8      0      0      0      0      0      0      

column2
2110630562
61      0      61      8      23      3      6      40      0      0      0      0      0      0      

Top Expert 2007

Commented:
Can you please indicate what they are then (using sql above)?
The error looks like the destination is a Datetime type.
Top Expert 2007

Commented:
That's saying that column2 is a datetime, whereas column1 is a char type..
how about

select top 10 Table2.COLUMN2, TABLE1.COLUMN1
FROM TABLE2 INNER JOIN
TABLE1 ON TABLE2.COLUMN1 = TABLE1.COLUMN1

Author

Commented:
column 1 is datetime type
column 2 is a 16 char

I need to copy from the datetime column to the char column
Top Expert 2007

Commented:
that's exactly the opposite of what syscolumns is saying!

Author

Commented:
ran that last command
Syntax error converting datetime from character string.
CERTIFIED EXPERT
Top Expert 2012

Commented:
That is because you have a date in a char column that cannot be converted to a date at least not implictly or with the expresion you are using.
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I just noticed I was comparing the wrong column once i read your last message. Thanks it worked.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.