Insert with a where

Question

Insert with a where

twdavis-893252

Ten Centuries

Points: 1195
More actions
December 23, 2008 at 1:55 pm

#127748

you can see from this what I would like to do but of course it does not work
INSERT INTO PS_CUST_REGN_TYPE (SETID,CUST_ID,REGION_CD_TYPE,REGION_CD,LAST_MAINT_OPRID,DATE_LAST_MAINT)
VALUES('CORP1','0000080005','TRPT','ARKANSAS','TWDAVIS','12/23/2008' )
where cust_id in(select cust_id from customers)
Thank You

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

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

Do you want to insert that row for every customer in the customers table with a match already in PS_CUST_REGN_TYPE? If this is what you want I think this code will work:

[font="Courier New"]INSERT INTO PS_CUST_REGN_TYPE

(

SETID,

CUST_ID,

REGION_CD_TYPE,

REGION_CD,

LAST_MAINT_OPRID,

DATE_LAST_MAINT

)

SELECT

'CORP1',

'0000080005',

'TRPT',

'ARKANSAS',

'TWDAVIS',

'12/23/2008'

FROM

customers C JOIN

PS_CUST_REGN_TYPE P ON

C.cust_id = P.cust_id[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 2

I have about 4000 rows of values to insert that are in a word doc that I will cut and paste into my sql statement but I do not want to insert into the PS_CUST_REGN_TYPE tables unless the customer is already in the customer table. so I don't create any orphans

CORP1','0000080050','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','0000080050-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','0000080108','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','0000080108-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','10221','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','1048','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','1048-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','10832-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','1148','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','1148-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','12163','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','12163-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','12356','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','12356-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','1458','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','1458-01','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

'CORP1','16030','TRPT','ARKANSAS','TWDAVIS','12/23/2008'

ETC

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

The first thing you are going to want to do is get that data into a usable format. That would be - put the data into a table in SQL Server. Once the data is in a table - it is very easy to build the insert statement to select from the table - with a join to customers as outlined by Jack.

One possibility is to create the table using SSMS - open the table, and then cut & paste from the Word doc into the table. I have done this using Excel data before - but not Word. It might work - might not.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

David Betteridge Hall of Fame Points: 3264 More actions · Answer 4

another trick is that if you can get the data into excel, then you can add a additional calculated column for generating an insert statement

for example

= "insert into xxxx (col1, col2) select " & A1 & "," & B2

you can then just cut and paste the generated sql into ssms.