Inserts....

Hi..
I have a problem...
I have a temporary table and I'm traying to do this...
create table #tmp
( valor varchar(100),
valor1 varchar(100),
valor2 varchar(100)
)

declare @.cmd varchar(500)

select @.cmd = 'Select valor, valor2 from mytable)

insert into #tmp(valor, valor2)
exec(@.cmd)

select valor, valor2 from #tmp

but I have an error... 'Invalid object name #tmp'

why??other than a few typos everything looked good try this:

if object_id('mytable') is not null drop table mytable
if object_id('tempdb..#tmp') is not null drop table mytable

create table mytable(
valor varchar(100),
valor1 varchar(100),
valor2 varchar(100))

insert into mytable values('A','B','C')
insert into mytable values('D','E','F')
insert into mytable values('G','H','I')
insert into mytable values('J','K','L')

create table #tmp(
valor varchar(100),
valor1 varchar(100),
valor2 varchar(100))

declare @.cmd varchar(500)

select @.cmd = 'Select valor, valor2 from mytable'

insert into #tmp(valor, valor2)
exec(@.cmd)

select valor, valor2 from #tmp|||If you have this code inside of a stored proc it should work, otherwise it won't because your temporary table is local not global. (local temporary tables are visible only in the current sesion).
Try using:
CREATE TABLE ##tmp

or

CREATE TABLE tempdb..tmp

see "tempoary tables" in BOL for more details

Inserting varchar to Decimal/Numeric

Hi,

Can we insert varchar values to Decimal(9,2)/numeric(9,2) fields?

I've a temp table with varchar values. I checked using isnumeric and all the values are numeric. But when I do the insert, it fails.

I was able to insert the following values:

000290165
000501075
000290165
000326314

But NOT the following:

010773474

All the values are 9 digits only.......

How can I convert this kind of numbers?

I need to convert all the 9 digit numbers to XXXXXXX.xx format.

(7digits.2 digits)

Thanks,

Siva.

I believe that in this case your expectations are incorrect. When I run:

Code Snippet

select inpt,
convert(numeric(9,2), inpt)
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 290165.00
000501075 501075.00
000290165 290165.00
000326314 326314.00

(5 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
*/

The output is as expected. But I don't think that is what you are expecting. To get this correct try:

Code Snippet

select inpt,
convert(numeric(9,2), left(inpt,7) + '.' + right(inpt,2))
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 2901.65
000501075 5010.75
000290165 2901.65
000326314 3263.14
010773474 107734.74
*/

|||

Wow!! Great!

It worked like a magic!!

Thanks much...

Siva.

Inserting values

Dear All,

In sql-server2000
In a table i am having a column of datatype varchar(8000).
While inserting the record through executenonquery, i am insert only
255 characters rest of the characters are getting trucated.

My question in how i will able to insert the row of that particular
column more than 255 characters

Thanx in advance.

Regardsfix the query.

i dunno what the query is or the command parameter info, so you'll just have to read on how to change the length of that sqldbtype.|||Dear Kragie,

thanx for your reply.

i am using command parameter info is command text and and directly using "Insert command".

Regards

Inserting the diff rows into a table

Hi,

I have two tables as follows

Table TempTab

{

CatId varchar(20),

lastupdate Datetime

}

Table MainTab

{

CatId varchar(20),

lastupdate Datetime

}

and the data in those tables are as follows

Table TempTab

{

CatId LastUpdate

Cat1 D1

Cat2 D2

Cat3 D3

Cat4 D4

}

Table MainTab

{

CatId LastUpdate

Cat1 D1

Cat3 D3

Cat5 D5

}

I need a query to insert the differences into the MinTab, i mean to say the fincal MainTab should look like as follows

Table MainTab

{

CatId LastUpdate

Cat1 D1

Cat2 D2

Cat3 D3

Cat4 D4

Cat5 D5

}

can any one please let me know the query

Thanks alot

~Mohan

INSERT INTO MainTab(CatID, LastUpdate)
SELECT CatID, LastUpdate FROM TempTab
WHERE CatID NOT IN(SELECT CatID FROM MainTab WHERE atID IS NOT NULL)
|||

Here it is,

Code Snippet

Create Table #temptab (

[CatId] Varchar(100) ,

[LastUpdate] Varchar(100)

);

Insert Into #temptab Values('Cat1','D1');

Insert Into #temptab Values('Cat2','D2');

Insert Into #temptab Values('Cat3','D3');

Insert Into #temptab Values('Cat4','D4');

Create Table #maintab (

[CatId] Varchar(100) ,

[LastUpdate] Varchar(100)

);

Insert Into #maintab Values('Cat1','D1');

Insert Into #maintab Values('Cat3','D3');

Insert Into #maintab Values('Cat5','D5');

Code Snippet

Insert Into #maintab

Select * from #temptab t

Where not exists

(

select 1 from #maintab m

where m.catid=t.catid

)