Multiple Dynamic Inserts with SQL

I'm try to a multiple insert from one database to another by using this code:

insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)
(selecttop 30 acno,surname,name,id,type
from [PretoriaDB].[dbo].[WorkingDB])

but I keep on getting this error:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Can any one help!!

If the target table will always be empty, you can just drop the table before the insert and use a "Select Into" statement. Otherwise, you will probably have to open a cursor for the "select top 30 ..." statement and iterate over it to insert each record to the table.

|||

tmametja:

String or binary data would be truncated.

the Error says that...

One of the fields on table [mpis].[dbo].[Residents] of type varchar/char is beingfed data that is too long. i.e. One of the Firleds in [PretoriaDB].[dbo].[WorkingDB] that you are usingto populate the [mpis].[dbo].[Residents] probably contains a string value that is too large for the field thatyou are trying to plug it into.

hope it helps to solve you problem./.

|||

i didn't know 2005 had an "INSERT...SELECT" statement... cool

Anyhow, looks like the syntax is INSERT [table_name] SELECT [colA], [colB] ... FROM [table_name_or_join]

I didn't see any "INTO" in the examples, or any mention of the target column list on the target table as in a tradional insert statement.

|||

tmametja:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Make sure data types (in INSERT and SELECT) are match and field length as well.

Good luck.

|||

thanx

|||

thanxkaushalparik27

|||

tmametja:

thanxkaushalparik27

well pleasure to help you, and dont forget to mark the answers which helped you and resolve the thread. Thanx