insert into a table from using data from other tables
-
HI,
I have a table in which there is data and it can be searched by JOBNumber.
I want to select all the records that have a jobno of, say, 50, 51,52,53,54,and 55 and take all of these records and put them into a single table. I know you can do the 'select into' which creates the table but I am unsure of the overall syntax. Can someone help?
thanks,
Paul
-
Have you looked at BOL and searched for SELECT INTO....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Yes, checked BOL but not seeing how to put multiples in - i.e.
select *
into TEST
from tblOrders
Where jobno = '50,51,52,53,55,56'
The result is that it is telling me that it cannot convert varchar value '50,51,52,53,55,56' to column of data type int
I don't want to have to do a select for each job no - if you see what I mean.
thanks
-
there is a "split" function in the scripts section that is able to split a string in seperate items
A stored procedure could
1) insert the splitted items in a temporary table
2) use the temporary to join with your dataset
like
select *
into TEST
from tblOrders
inner join #SelectedOrderId selecteditems
on tblOrders.jobno=selecteditems.jobno
small remarks:
select * is not recommended
specify the owner of the table so it can reuse its query plan
-
Am assuming here that ur wanting all the records for job 50 AND 51 AND 52 ... etc??
The syntax i'd use is
INSERT INTO myNewTable (field1, Field2, Field3 )
SELECT aField, bField, cField
FROM tblOrders o
WHERE o.jobno IN (50, 51, 52, 53, 54)
From what i've read, i think it's the IN clause u've been missing out on! You will have to create myNewTable manually first, as this syntax wont create it ... but i'm assuming that isn't too much of a hardship!?
-
Yeah, the IN clause is what you're missing.
Here is a sample (pretty much like above , but I had it laying around)
You can substitute a "real" table for @holder.
Use pubs
Go
declare @holder table (
myid varchar(16) ,
mytitle varchar(64)
)
INSERT INTO @holder ( myid , mytitle )
Select
title_id ,
title
From
titles
Where
title_id IN ( 'PC1035' , 'PS2091' )
Select myid , mytitle from @holder
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply