confused on a select into..

Question

confused on a select into..

Jpotucek

SSCoach

Points: 18583
More actions
January 30, 2008 at 1:00 pm

#182382

Ran this to select 30 records from one table.
select * from tower.nbdocdeletedrecords with (nolock)
where PresentDate > '2008-01-01' and presentdate < '2008-01-19'
and [systemuser] = 'tower'
and polnum like '00u%'
and country ='50'
need to insert those 30 records into a second table EXCLUDING the first 2 and last 2 columns..
Here's a start (I think)
insert into tower.nbdoc
select * from tower.nbdocdeletedrecords with (nolock)
where PresentDate > '2008-01-01' and presentdate < '2008-01-19'
and [systemuser] = 'tower'
and polnum like '00u%'
and country ='50'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

You'll have to name those columns (in the exact sequence as needed for your target table) from the source table.

Using query analyser, in the object explorer, rightclick the source table, and then choose script as select to clipboard/new query window and then remove the columns you don't need.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Pretty much, assuming the table tower.nbdoc exists.

You just need to specify the column names for the insert and in the select. eg

INSERT INTO ATable (Col1, col2, col3, col4)

SELECT Value1, value2, value3, value4 FROM SomeOtherTable.

Does that make sense?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jpotucek SSCoach Points: 18583 More actions · Answer 3

something like this:

insert into tower.nbdoc (npages, ifnds, ifnid, datatype, win, status, polnum, multi, slevel, descript, scandate, boxid, username, batchtyp, memfnam, miscflag, spare1, spare2, date1, agent, batchnam, country, qaoper, indxoper, claimnum, memnum, dob, spare3, spare4, date2, date3, int1, int2)

select (npages, ifnds, ifnid, datatype, win, status, polnum, multi, slevel, descript, scandate, boxid, username, batchtyp, memfnam, miscflag, spare1, spare2, date1, agent, batchnam, country, qaoper, indxoper, claimnum, memnum, dob, spare3, spare4, date2, date3, int1, int2)

from tower.nbdocdeletedrecords with (nolock)

--where polnum = '00u92552'

where PresentDate > '2008-01-01' and presentdate < '2008-01-19'

and [systemuser] = 'tower'

and polnum like '00u%'

and country ='50'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Looks good.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jpotucek SSCoach Points: 18583 More actions · Answer 5

Jpotucek

SSCoach

Points: 18583

January 30, 2008 at 1:30 pm

#773509

you guys are awesome !!! Thank you!