insert into table

17 Aug 2006 10:13 PM

Zeng

Hi,

I thought we can just do this insert...into statement to copy data from one
table in another db but it gives me errors below. Both product tables have
the same schema.

insert into db1.dbo.product
( select * from db2.dbo.product )

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

Is it possible to avoid specifying all the column names? thanks!

-zeng

17 Aug 2006 10:20 PM

Tom Cooper

You don't have to specify the column names, but it is generally good
practice to do so. But the syntax error is because you have ( ) around the
select part.

Try either

insert into db1.dbo.product (<list of column names>)
select <list of column names> from db2.dbo.product

or

insert into db1.dbo.product
select * from db2.dbo.product

Tom

Show quote

"Zeng" <zeng@nononospam.com> wrote in message
news:uyihppkwGHA.2208@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I thought we can just do this insert...into statement to copy data from
> one table in another db but it gives me errors below. Both product tables
> have the same schema.
>
> insert into db1.dbo.product
> ( select * from db2.dbo.product )
>
>
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'select'.
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near ')'.
>
> Is it possible to avoid specifying all the column names? thanks!
>
> -zeng
>

17 Aug 2006 10:23 PM

Immy

remove the brackets.
Show quote

"Zeng" <zeng@nononospam.com> wrote in message
news:uyihppkwGHA.2208@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I thought we can just do this insert...into statement to copy data from
> one table in another db but it gives me errors below. Both product tables
> have the same schema.
>
> insert into db1.dbo.product
> ( select * from db2.dbo.product )
>
>
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'select'.
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near ')'.
>
> Is it possible to avoid specifying all the column names? thanks!
>
> -zeng
>