Inserting with a select * from statement returns Error

[bmacbmac]

Hi,

I am trying to insert data from one DB into another DB with the following statement:

insert into db2..tblparty
select * from db1..tblparty
join tbldocument
on tbldocument.irecordid = tblparty.irecordid
where tbldocument.irecordid between 1 and 1000

I get an error: Insert Error: Column name or number of supplied values does not match table definition.

Although both tables have 9 columns which are set up the exact same.

Is there a way to do this without listing each column individually? I have about 30 tables to insert and each table has a lot of columns.

Thanks,

Brian

 

[SQLDenis]

try this

insert into db2..tblparty
select t.* from db1..tblparty t
join tbldocument d
on d.irecordid = t.irecordid
where d.irecordid between 1 and 1000

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

The code that SQLDenis posted should do the trick. This is the problem with doing a select *.

When you do a select * without specifing the table name before the * (t.* in this example) you are getting all the columns from both tables involved in the join, not just the table in the from statement.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bmacbmac]

That worked perfectly. Thanks to you both, you have been a great help to me this year.

 

[mrdenny]

no problem

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com