INSERT-SELECT generates odd results

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

SQL Server Development (2000)

INSERT-SELECT generates odd results

Author

Topic

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-28 : 12:48:09

Has anyone seen anything like this at all? I'm running the following query:

INSERT CMC_CLST_STATUS
(CLCL_ID,
CLST_SEQ_NO,
MEME_CK,
CLST_STS,
USUS_ID,
CLST_STS_DT,
CLST_MCTR_REAS,
CLST_USID_ROUTE,
CLST_LOCK_TOKEN )
SELECT TOP 10 
CLCL_ID,
CLST_SEQ_NO = 1,
MEME_CK,
CLST_STS = CASE CL_STATUS
	WHEN '12' THEN '16'
	WHEN '16' THEN '11'
	ELSE CL_STATUS
	END,
USUS_ID = 'batchuser',
CLST_STS_DT = '1/1/1753',
CLST_MCTR_REAS = ' ',
CLST_USID_ROUTE = ' ',
CLST_LOCK_TOKEN = 1
FROM	fadbnfimport.dbo.IMX_CLHD_CLAIM
WHERE	MEME_CK IS NOT NULL

which should insert 10 records in the CMC_CLST_STATUS table. I normally run it without the top 10 as there are 200,000+ records in the source table. But I'm trying to duplicate the problem. The result I get is:


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(0 row(s) affected)


(10 row(s) affected)

It should finish in seconds. It's slow -- up to a minute. With the full 200,000 records it ran overnight and didn't finish. The 200,000 record insert right above it ran in just a few minutes. I can't figure out why it's returning what appear to be multiple record sets. The target table is empty when the query runs. It's a SQL7 SP3 case-sensitive server on NT4 SP(?). Anyone have any thoughts?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-28 : 13:01:06

I hate triggers!

Darn thing was firing a trigger for every row it inserted. That's what you get for working with a database you don't know forward and backwards. Oh well!

I really need some

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2001-11-28 : 15:03:11

I think graz was just looking for an excuse to use the beer chugging smiley.

Justin

mfemenel
Professor Frink

1421 Posts

Posted - 2001-11-28 : 15:09:17

Notice he's keeping the good icons for himself! Come on Graz, since you're upgrading things! Give us da juice!

Mike
"A program is a device used to convert data into error messages."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-28 : 15:22:44

the icon above is "chug" in square brackets []

The beer icon is "beer" in square brackets []

mmmmmmm...synchronized chugging!

Edited by - robvolk on 11/28/2001 15:23:44

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-28 : 15:48:27

And there's another icon I'm waiting to spring our unsuspecting readers...

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

mfemenel
Professor Frink

1421 Posts

Posted - 2001-11-28 : 15:55:08

Cool!
Unfortunately, now I'm thirsty.

Mike
"A program is a device used to convert data into error messages."

Edited by - mfemenel on 11/28/2001 15:55:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources