mbroad02
asked on
SQL SERVER-write to temp table and delete from primary table
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
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
do u have any primary key on the 'MAX_WITH_CC ' table
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)
ASKER
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?
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?
WEHERE s/b WHERE
I copied and pasted your code, didnt' notice the spelling mistake until now.
I copied and pasted your code, didnt' notice the spelling mistake until now.
ASKER
The table does not have a KEY...let me try the version you wrote last (about two comments above this one)---thanks
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.
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.
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
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.
ASKER
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)
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)
INSERT INTO CC01
output inserted.cc into #temp
SELECT 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 #temp)
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
using the temp table will stick to the true nature of what your question asks.
Don't forget to:
DROP TABLE #temp
ASKER
Realizing that you said to use a temp table, why wont the below query work? I ran it and no tables changed...?
Thanks
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)
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
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
ASKER
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????
(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......................
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......................
ASKER
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?
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?
Exactly, there you go :)
ASKER
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!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!
(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
>(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
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
ASKER
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?
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
ASKER
Awesome!! Awesome!! Awesome!! thank you so much for ALL of your help!! It worked wonderfully!
ASKER
Excellent assistance!! thank you for ALL of your hard work in solving this issue.
Glad that we could help
ASKER
CREATE TABLE [dbo].[CC01-temp] (
[NAME] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_
[ADDRESS] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_
[CITY] [char] (19) COLLATE SQL_Latin1_General_CP1_CI_
[STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_
[ZIP] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_
[ZIP4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_
[DATEOFBIRTH] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_
[SEX] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_
[SID] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_
[CC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_
) 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?