how to insert multiple values which is the output of a select query

Question

Select 
cast(ltrim(rtrim(Substring(string,charindex('my',string)+len('my')+5,
charindex('for the company',string)-charindex('my',string)+len('my')-9))) as datetime)
 from table

Select 
cast(ltrim(rtrim(Substring(string,charindex('company',string)+len('company')+1,
len(String)-charindex('company',string)+len('company')-9)))as varchar) 
from description

this 2 queries has a set of rows as output.

I want to insert these values to another table using single insert. What i did is:

insert into table2(orderid,orderdate) 
 Select
cast(ltrim(rtrim(Substring(String, len('The order number')+1,
CHARINDEX ( 'as been created at', String) - len ('as been created at')))) as int)
,
cast(ltrim(rtrim(Substring(string,charindex('at',string)+len('at')+5,
charindex('for the company',string)-charindex('at',string)+len('at')-9))) as datetime)
from description

but its not inserted..its showing error like The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.. Is there any another way to insert this?

string is The order number 634528 has been created at 2011-03-02 21:11:15.367 for the company Toyato — agnes
– agnes, Commented Mar 8, 2011 at 6:44

Simon · Accepted Answer · 2011-03-08 03:58:54Z

1

when inserting a datetime you should specify a string that is SQL compliant i.e.

'YYYY-MM-DD HH:MM:SS'

I suspect that the CAST function cant convert the type specified. However you dont need to convert to a datetime, you can simply insert the string representation of the datetime.

Other issues you may encounter are the casting of ltrim(rtrim()) to an int. LTRIM, RTRIM both return a varchar. CHARINDEX already returns an int.

Ensure your types are consistent

answered Mar 8, 2011 at 3:58

Simon

9,42515 gold badges79 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anil Soman · Accepted Answer · 2011-03-08 04:06:42Z

0

To insert output from your two queries into a single table, the data types and number of columns from these queries should match with that of the table. You can use convert

CONVERT(
  DATETIME,ltrim(rtrim(Substring(
  string,charindex('at',string)+len('at')+5, 
  charindex('for the company',string)-charindex('at',string)+len('at')-9)))
,112)

for your datetime column and try (provided your target table has a datetime column for OrderDate).

answered Mar 8, 2011 at 4:06

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

2 Comments

agnes Over a year ago

please help me... Why you are not accepting questions from my Account? Kindly let me know about this. please.

Anil Soman Over a year ago

@agens: What are you talking about? I did not get what you are asking ... which questions?

Collectives™ on Stack Overflow

how to insert multiple values which is the output of a select query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related