Datatype issues with insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Datatype issues with insert

Author

Topic

kyma
Starting Member

11 Posts

Posted - 2008-08-06 : 06:07:53

I am trying to set up a a linked server so that an MSSQL database will automatically update a MySQL. I'm having some trouble scripting the initial insert queries. I think it is a data-type issue but have been unable to resolve it so far. To begin with I have two columns
item id (int)
title (nvarchar(450))

which I would like to push into columns:

nid (int(10))
title (varchar(255))

So far I have the following query:

insert
openquery(ontopicz_link,
'select nid,
title
from node')

select
convert(int(10), itemid),
convert(varchar(255), title)

from MSSQLDatabase.dbo.items

But this returns the error:
CAST of CONVERT: invalid attributes specified for type 'int'
Can anyone offer some advice on this?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-06 : 07:06:41

you don't need this...

select
convert(int(10), itemid),
convert(varchar(255), title)

and i should think you may encounter problems inserting into a varchar(255) column when you source column is so much longer, you'll probably need to do a substring to cut it down?
Em

kyma
Starting Member

11 Posts

Posted - 2008-08-06 : 07:26:23

Ok thanks,

Now I've got:

insert
openquery(ontopicz_link,
'select nid,
title
from node')
select
convert (int, itemid),
convert (varchar(225), substring(title,1,225))
from MSSQLDatabase.dbo.items

but it says the OLE DB provider "MSDASQL" for linked server "ontopicz_link could not insert into table "[msdasql]"

Any ideas?

kyma
Starting Member

11 Posts

Posted - 2008-08-06 : 09:50:43

Ok, I think it's maybe because there are additional columns in the target table which are not null. How can I do an openquery insert using a mixture of selects and static values?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-06 : 09:52:36

you can select a literal value along with any columns like...

select column1, 'test' as column2, 3 as column3
from YourTable

Em

kyma
Starting Member

11 Posts

Posted - 2008-08-06 : 10:24:46

do the column names have to relate to my source table columns or the target table? There are a different number of columns in the tables too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 14:01:05

quote:
Originally posted by kyma

do the column names have to relate to my source table columns or the target table? There are a different number of columns in the tables too.

no problem if you're using different name. Make sure the type of data inserting coincides with target table field datatype. Also make sure you pass values for all not nullable fields.You can leave others balnk no problem. just dont include them in column list also.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources