DTS with select and insert statements w/ No Null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS with select and insert statements w/ No Null

Author

Topic

petepinca
Starting Member

2 Posts

Posted - 2005-12-01 : 13:57:28

Okay - anyone looking for a challenge? We're migrating basic parts data from one SQL db to another using DTS. The resulting select statement would work (parses fine and we can see sample data) BUT the destination table has fields with the no nulls property. These fields are irrelavent data wise and we tried to populate them with an insert statement but of course, the package never gets to run the insert because the select part fails when it detects the first row has no data in the not null fields. Is there code or DTS options changes we can use that would allow the insert statement to run - and fill the row with necessary data - so that the regular (relevent) data can come over via the selct statement - without failing. (Error message about the column not allowing nulls?

Here's what it looks like:

PRTCAT
SELECT si_prod_id, si_prod_desc, si_prod_units, unique_id
FROM si_prod_mstr

PRTSTK
SELECT sii_avg_cost, si_whse_id, unique_id
FROM
sii_invtry_dtl

PRTAREA
SELECT si_whse_id, si_whse_desc
FROM si_whse_mstr

INSERT
INTO IMSV7.PRTCAT
(DELETED, PRTAKIT,
PRTCRITICL, PRTLOTTRCK, PRTONCNTCT, PRTSERIAL, PRTSTKED)
VALUES
(1, 'N', 'N', 'N', 'N', 'N', 'N')

Be a hero - we'll pay it forward!

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-12-01 : 23:17:37

Welcome To SQLTEAM.COM !!!

Still I'm not much clear on what you are trying to do...
but..may be using IF Exists will help?
Can you post the table structure and the likely result?

Thanks
Karunakaran

petepinca
Starting Member

2 Posts

Posted - 2005-12-02 : 19:45:45

Not sure the table structure would help. The two dbs are similar in that they contain repair parts for our water/wastewater plant. Field types, key fields, etc are all compatible and properly mapped.

The select statement is generated by DTS. It would work if there weren't fields we don't really need data for but have a Not Null property. So I can't create the records in the destination table because I have no data for these fields. (Within the Source tables) I don't want to do a "stand alone" insert statement for these Not Null fields (I know what data to fill them with - mainly "N" for "NO".)If I did, my basic DTS Select statement wouldn't work - I'd have to do some kind of append statement outside of DTS. I want a reusable package that runs the entire migration.

I need VB that combines the Select and Insert so that the Select doesn't try to run separately - but waits at each row for the Insert statement to help complete the row. (Fill the Not Nulls)

THANKS!

J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources