Data transferring problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Data transferring problem

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-02-18 : 13:41:14

I am tranferring some data from an old database to a new one.
The table structure is little different. So, I can't just move the whole data to the new one.
When doing this - I found that the old one has some duplicate entries not liked by my new key. Here's the query:
INSERT INTO ABC_LOG (ABC_NUM,ABC_TYPE,ABC_STATION,ABC_PARAM,ABC_START_DATE,ABC_START_TIME,ABC_METHOD,ABC_PARAM_LEVEL,ABC_PARAM_UNIT,ABC_GUIDE_LEVEL,ABC_GUIDE_UNIT,.............)
SELECT XYZ_SAMPLE_NUM,XYZ_TYPE,XYZ_STN_ID,XYZ_PARAM_ID, XYZ_START_DATE,XYZ_START_TIME,XYZ_METHOD,XYZ_PARAM_LEVEL,
XYZ_PARAM_UNITS,............. FROM XYZ_LOG,STN_INFO
WHERE XYZ_SITE_ID = STN_PLOTSITE_ID AND XYZ_PLOT_ID = STN_PLOT_ID

Among the fields in ABC_LOG table, first 6 are primary key fields.
If I use DISTINCT instead of just select, I get less duplication - but I still get some and using DISTINCT on all these columns is not correct because all of them are not the key fields or unique constrained.

Any idea how I can do this? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 13:46:02

use like this. but just keep in mind that it gives randomly selected value for all of other non key fields

INSERT INTO ABC_LOG (ABC_NUM,ABC_TYPE,ABC_STATION,ABC_PARAM,ABC_START_DATE,ABC_START_TIME,ABC_METHOD,ABC_PARAM_LEVEL,ABC_PARAM_UNIT,ABC_GUIDE_LEVEL,ABC_GUIDE_UNIT,.............)
SELECT XYZ_SAMPLE_NUM,XYZ_TYPE,XYZ_STN_ID,XYZ_PARAM_ID, XYZ_START_DATE,XYZ_START_TIME,MAX(XYZ_METHOD,XYZ_PARAM_LEVEL),
MAX(XYZ_PARAM_UNITS),............. FROM XYZ_LOG,STN_INFO 
WHERE XYZ_SITE_ID = STN_PLOTSITE_ID AND XYZ_PLOT_ID = STN_PLOT_ID
GROUP BY XYZ_SAMPLE_NUM,XYZ_TYPE,XYZ_STN_ID,XYZ_PARAM_ID, XYZ_START_DATE,XYZ_START_TIME

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-02-18 : 17:57:34

Sorry - what do you mean by "it gives randomly selected value for all of other non key fields"?
We don't want random values right?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 03:17:53

suppose you've 4 records with same pk values, then using myquery will give you any of values in 4 rows for non key columns

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-02-20 : 18:00:19

Still fails...saying - "Violation of PRIMARY KEY constraint 'PK_ABC_LOG'. Cannot insert duplicate key in object 'dbo.ABC_LOG'. The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation."

Any other way?
Thanks visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:23:05

[code]
INSERT INTO ABC_LOG (ABC_NUM,ABC_TYPE,ABC_STATION,ABC_PARAM,ABC_START_DATE,ABC_START_TIME,ABC_METHOD,ABC_PARAM_LEVEL,ABC_PARAM_UNIT,ABC_GUIDE_LEVEL,ABC_GUIDE_UNIT,.............)
SELECT XYZ_SAMPLE_NUM,XYZ_TYPE,XYZ_STN_ID,XYZ_PARAM_ID, XYZ_START_DATE,XYZ_START_TIME,MAX(XYZ_METHOD,XYZ_PARAM_LEVEL),
MAX(XYZ_PARAM_UNITS),............. FROM XYZ_LOG t1,STN_INFO t2
WHERE XYZ_SITE_ID = STN_PLOTSITE_ID AND XYZ_PLOT_ID = STN_PLOT_ID
AND NOT EXISTS(SELECT 1 FROM ABC_LOG
WHERE XYZ_SAMPLE_NUM = {t1/t2}.XYZ_SAMPLE_NUM
AND XYZ_TYPE = {t1/t2}.XYZ_TYPE
AND XYZ_STN_ID = {t1/t2}.XYZ_STN_ID
AND XYZ_PARAM_ID = {t1/t2}.XYZ_PARAM_ID
AND XYZ_START_DATE = {t1/t2}.XYZ_START_DATE
AND XYZ_START_TIME = {t1/t2}.XYZ_START_TIME
)
GROUP BY XYZ_SAMPLE_NUM,XYZ_TYPE,XYZ_STN_ID,XYZ_PARAM_ID, XYZ_START_DATE,XYZ_START_TIME
[/code]

i'm not sure which table out of XYZ_LOG & STN_INFO contain the various columns that form pk of ABC_LOG. so make sure you rteplace t1/t2 with correct alias (t1 if coming from XYZ_LOG and t2 if coming from STN_INFO )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources