Solved

SQL SERVER-write to temp table and delete from primary table

Posted on 2009-05-11
27
391 Views
Last Modified: 2012-05-06
I am interested in modifying the attached SQL SERVER query (attached).  the current query will read from the MAX_WITH _CC table (a customer table) and write the top n "01" records to CC01 (customer by jurisdiction--CC is a jurisdiction code).  This worls fine, but I would like to add one additional feature: as each "01" row is written out, I would like to delete the original row from the MAX_WITH_CC table.
Can you explain how to do that?
thanks
INSERT INTO CC01

     SELECT DISTINCT top 2100

	MAX_WITH_CC.NAME,  

	MAX_WITH_CC.ADDRESS, 

	MAX_WITH_CC.CITY,

	MAX_WITH_CC.STATE, 

	MAX_WITH_CC.ZIP, 

	MAX_WITH_CC.ZIP4, 

	MAX_WITH_CC.DATEOFBIRTH, 

 	MAX_WITH_CC.SEX, 

	MAX_WITH_CC.SID, 

	MAX_WITH_CC.CC

     FROM MAX_WITH_CC

        WHERE MAX_WITH_CC2.CC = '01'

ORDER BY MAX_WITH_CC.ZIP;

Open in new window

0
Comment
Question by:mbroad02
  • 11
  • 10
  • 6
27 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 24358285
create table #temp (sid int )

INSERT INTO CC01
OUTPUT inserted.sid into #temp   ---assuming that sid is unique
SELECT DISTINCT top 2100
      MAX_WITH_CC.NAME,  
      MAX_WITH_CC.ADDRESS,
      MAX_WITH_CC.CITY,
      MAX_WITH_CC.STATE,
      MAX_WITH_CC.ZIP,
      MAX_WITH_CC.ZIP4,
      MAX_WITH_CC.DATEOFBIRTH,
       MAX_WITH_CC.SEX,
      MAX_WITH_CC.SID,
      MAX_WITH_CC.CC
FROM MAX_WITH_CC
WHERE MAX_WITH_CC2.CC = '01'
ORDER BY MAX_WITH_CC.ZIP;

delete MAX_WITH_CC
from MAX_WITH_CC c
inner join #temp t on c.sid = t.sid
0
 

Author Comment

by:mbroad02
ID: 24358384
Ok, I tried it like this (I don't match on sid but on CC):
CREATE TABLE [dbo].[CC01-temp] (
      [NAME] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ADDRESS] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CITY] [char] (19) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ZIP] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ZIP4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DATEOFBIRTH] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SEX] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SID] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY]




INSERT INTO CC01
OUTPUT CC01-temp
     SELECT DISTINCT top 2100
      MAX_WITH_CC.NAME,  
      MAX_WITH_CC.ADDRESS,
      MAX_WITH_CC.CITY,
      MAX_WITH_CC.STATE,
      MAX_WITH_CC.ZIP,
      MAX_WITH_CC.ZIP4,
      MAX_WITH_CC.DATEOFBIRTH,
       MAX_WITH_CC.SEX,
      MAX_WITH_CC.SID,
      MAX_WITH_CC.CC
     FROM MAX_WITH_CC
        WHERE MAX_WITH_CC2.CC = '01'

delete MAX_WITH_CC
from MAX_WITH_CC c
inner join CC01-temp t on c.CC = t.CC
ORDER BY MAX_WITH_CC.ZIP;
     

There is a problem with the WRITE (an error)---Any suggestions?
0
 
LVL 9

Assisted Solution

by:Hwkranger
Hwkranger earned 100 total points
ID: 24358417
Don't use Order by when deleting.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24358445
do u have any primary key on the 'MAX_WITH_CC ' table
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24358448
See below:
INSERT INTO CC01

SELECT DISTINCT top 2100

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC2.CC = '01'
 

delete 

from MAX_WITH_CC 

WEHERE CC IN (SELECT CC FROM CC01)

Open in new window

0
 

Author Comment

by:mbroad02
ID: 24358449
Ok, but I still get this error for the "OUTPUT" line:

Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'OUTPUT'.
Server: Msg 170, Level 15, State 1, Line 35
Line 35: Incorrect syntax near '-'.

Do I have the "OUTPUT" line coded right?
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24358460
WEHERE s/b WHERE

I copied and pasted your code, didnt' notice the spelling mistake until now.
0
 

Author Comment

by:mbroad02
ID: 24358471
The table does not have a KEY...let me try the version you wrote last (about two comments above this one)---thanks
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24358508
A couple pointers:

1) don't use #temp as a temp table.  if it fails, you'll have a garbage table out there for a while until you kill your session.  Use @temp instead.  Use global and local tables appropriately, it keeps life easy.
2) Don't use output, it's good for some situations, here it's just an extra step.  
3) If you have a primary ( or alternate) key, use it.  In this case it's CC (or so you've told us)
4) Keep it simple.

best of luck.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24358511
i strongly suggest you create a Unique key for that table, like an identity column, which will  not only makes your query execute faster but also will delete the exact data
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24358527
If there is NO PRIMARY KEY you are TSOL.  YOu have to define one, or you'll delete data you dont' want to delete.  A primary key does not have to be specifically defined, but in the sense of the uniqueness of the data it does need to be defined.  
0
 

Author Comment

by:mbroad02
ID: 24358619
ok, if I define MAX_WITH_CC and CC01 with a prim key of the column CC, will the below code work (with using a temp table)????
INSERT INTO CC01

SELECT DISTINCT top 2100

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC2.CC = '01'

 

delete 

from MAX_WITH_CC 

WEHERE CC IN (SELECT CC FROM CC01)

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24358734
I prefer you use the temptable


INSERT INTO CC01
output inserted.cc into #temp
SELECT  top 2100   --------------------------- you dont need distinct here as CC is unique
     MAX_WITH_CC.NAME,  
     MAX_WITH_CC.ADDRESS,
      MAX_WITH_CC.CITY,
     MAX_WITH_CC.STATE,
      MAX_WITH_CC.ZIP,
      MAX_WITH_CC.ZIP4,
      MAX_WITH_CC.DATEOFBIRTH,
       MAX_WITH_CC.SEX,
      MAX_WITH_CC.SID,
      MAX_WITH_CC.CC
    FROM MAX_WITH_CC
       WHERE MAX_WITH_CC2.CC = '01'
 
delete
from MAX_WITH_CC
WEHERE CC IN (SELECT CC FROM #temp)
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 9

Expert Comment

by:Hwkranger
ID: 24358979
Using the temp table will delete only the records that you just pulled.  vrs deleting all the records in the "new"/"migrated" data table.

using the temp table will stick to the true nature of what your question asks.

Don't forget to:

DROP TABLE #temp
0
 

Author Comment

by:mbroad02
ID: 24364003
Realizing that you said to use a temp table, why wont the below query work?  I ran it and no tables changed...?
Thanks
INSERT INTO CC01

SELECT DISTINCT top 2400

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '01'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC01)
 

INSERT INTO CC02

SELECT DISTINCT top 3000

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '02'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC02)
 

INSERT INTO CC03

SELECT DISTINCT top 1500

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '03'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC03)

      

INSERT INTO CC04

SELECT DISTINCT top 4200

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '04'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC04)

      

INSERT INTO CC05

SELECT DISTINCT top 3300

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '05'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC05)
 

INSERT INTO CC06

SELECT DISTINCT top 1600

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '06'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC06)
 

INSERT INTO CC07

SELECT DISTINCT top 1500

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '07'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC07)
 

INSERT INTO CC08

SELECT DISTINCT top 800

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '08'

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC08)

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24364114
I think that should work, in order to test, out a 'SELECT @@ROWCOUNT ' after each statement and check the results
 
INSERT INTO CC01
SELECT DISTINCT top 2400
     MAX_WITH_CC.NAME,  
     MAX_WITH_CC.ADDRESS,
      MAX_WITH_CC.CITY,
     MAX_WITH_CC.STATE,
      MAX_WITH_CC.ZIP,
      MAX_WITH_CC.ZIP4,
      MAX_WITH_CC.DATEOFBIRTH,
       MAX_WITH_CC.SEX,
      MAX_WITH_CC.SID,
      MAX_WITH_CC.CC
    FROM MAX_WITH_CC
       WHERE MAX_WITH_CC.CC = '01'
SELECT @@ROWCOUNT
delete
from MAX_WITH_CC
WHERE CC IN (SELECT CC FROM CC01)
SELECT @@ROWCOUNT

0
 

Author Comment

by:mbroad02
ID: 24366196
Query is working!!  BUTTTTTTTTT....I think I DO need to use a temp table however because the delete ends up deleting EVERYTHING on the original table.


(Look at attached code---I am a bit of a SQL novice so could you please explain how to define use temp table?)   Is this coded correctly????

CREATE TABLE [@-temp] (

[NAME] [char] (36) NULL ,

[ADDRESS] [char] (36) NULL ,

[CITY] [char] (19) NULL ,

[STATE] [char] (2) NULL ,

[ZIP] [char] (5) NULL ,

[ZIP4] [char] (4) NULL ,

[DATEOFBIRTH] [char] (8) NULL ,

[SEX] [char] (1) NULL ,

[SID] [char] (13) NULL PRIMARY KEY,

[CC] [char] (2) NULL,

) ON [PRIMARY]
 

INSERT INTO CC01

output inserted.cc into #temp

SELECT  top 2100...................... 

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24366331

You said you creates a Prmary key on CC01, are you sure about this ?
 

CREATE TABLE [#temp] (

[PrimarykeyColumnOfTheCC01] 

) ON [PRIMARY]
 

INSERT INTO CC01

output inserted.PrimarykeyColumnOfTheCC01 into #temp

SELECT  top 2100...................... 

Open in new window

0
 

Author Comment

by:mbroad02
ID: 24366383
yes on both the original table (MAX_WITH_CC) and the output tables (CC01, etc)--the primary key is SID (which is unique)...

Ok, so I create the temp table with ONLY SID on it (as the key) and output the SID from the MAX_WITH_CC table to the @temp table????

And then after the INSERT statement I place:

deletefrom MAX_WITH_CCWHERE SID IN (SELECT SID FROM #temp)

Is this correct syntax?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24366408
Exactly, there you go :)
0
 

Author Comment

by:mbroad02
ID: 24366550
Oy Vey!!!   My code is attached.  I am really clode, but it doesn't like TWO things:

(1)  The OUTPUT line is incorrect (am I outputting only the SID field from the MAX_WITH_CC table?)

(2)  I create and drop the #temp table with each "CC"-iteration....  SQl doesn't lke that---says that there is already a #temp defeined (even though I drop it each time...



????


and THANKS A LOT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]

 

INSERT INTO CC01

output MAX_WITH_CC.SID into #temp

SELECT top 2400

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '1'

    Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE SID IN (SELECT SID FROM #temp)
 

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC02

SELECT top 3000

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '2'

 Order by NEWID()
 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC02)
 

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC03

SELECT top 1500

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '3'

Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC03)

      

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC04

SELECT top 4200

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '4'

Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC04)

      

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC05

SELECT top 3300

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '5'

Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC05)
 

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC06

SELECT top 1600

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '6'

Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC06)
 

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC07

SELECT top 1500

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '7'

Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC07)
 

DROP TABLE #temp
 

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]
 

INSERT INTO CC08

SELECT top 800

     MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE MAX_WITH_CC.CC = '8'

Order by NEWID()

 

delete 

from MAX_WITH_CC 

WHERE CC IN (SELECT CC FROM CC08)
 

DROP TABLE #temp

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24366626
>(1)  The OUTPUT line is incorrect
Are yu sureyou are working with SQL Serve r2005/ 2008, because OUTPUT feature is not available on sql 2000
>2)  I create and drop the #temp table with each "CC"-iteration....  SQl doesn't lke that---says that there is already a #temp defeined (even though I drop it each time...

Put a GO in bet ween the DROP and CREATE statements


 
0
 

Author Comment

by:mbroad02
ID: 24366667
Ok, mein feux paux!!  I guess I AM using SQL SERVER 2000 (that is what the box-banner says when I select "ABOUT")....Are there other options instead of OUTPUT?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24367193

CREATE TABLE [#temp] (

[SID] [char] (13) NOT NULL PRIMARY KEY

) ON [PRIMARY]

Insert into #temp

SELECT top 2400 SID 

FROM MAX_WITH_CC

WHERE MAX_WITH_CC.CC = '1'

Order by NEWID()
 

INSERT INTO CC01

SELECT  MAX_WITH_CC.NAME,  

     MAX_WITH_CC.ADDRESS, 

      MAX_WITH_CC.CITY,

     MAX_WITH_CC.STATE, 

      MAX_WITH_CC.ZIP, 

      MAX_WITH_CC.ZIP4, 

      MAX_WITH_CC.DATEOFBIRTH, 

       MAX_WITH_CC.SEX, 

      MAX_WITH_CC.SID, 

      MAX_WITH_CC.CC

    FROM MAX_WITH_CC

       WHERE SID IN (SELECT SID FROM #temp)
 

delete 

from MAX_WITH_CC 

WHERE SID IN (SELECT SID FROM #temp)
 

DROP TABLE #temp

Open in new window

0
 

Author Comment

by:mbroad02
ID: 24368707
Awesome!! Awesome!! Awesome!!  thank you so much for ALL of your help!!  It worked wonderfully!
0
 

Author Closing Comment

by:mbroad02
ID: 31580306
Excellent assistance!!  thank you for ALL of your hard work in solving this issue.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24368811
Glad that we could help
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now