Problem Inserting Unique Rows into a Table

[HughManity]

I'm using SQL Express 2005.

I have two tables, TABLEA and TABLEB. TABLEA has the following:

col1
-----
NAMEAAA
NAMEAAA
NAMEBBB
NAMEBBB
NAMECCC
NAMECCC
NAMECCC
NAMEDDD

I want to insert rows into TABLEB so that:

fld1 fld2 fld3 fld4 fld5
---- ----- ---- ---- ----
NAMEAAA 0 NULL 0.0000 NULL
NAMEBBB 0 NULL 0.0000 NULL
NAMECCC 0 NULL 0.0000 NULL
NAMEDDD 0 NULL 0.0000 NULL
================================================== ======

If I try the following, 8 rows get created in TABLEB:

INSERT INTO TABLEB
([fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5])
SELECT col1,0,NULL,0,NULL
FROM TABLEA
WHERE not exists (select * from TABLEB
where TABLEB.fld1 = TABLEA.col1);

If instead I try the following, 8 rows still get created:

INSERT INTO TABLEB
([fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5])
SELECT col1,0,NULL,0,NULL
FROM TABLEA
WHERE col1 NOT IN (select fld1 FROM TABLEB);
================================================== =

If I put the word DISTINCT before "col1" in my SELECT statement, I get the error, "The text, ntext, or image data type cannot be selected as DISTINCT". If I remove the "col1,0,NULL,0,NULL" from the SELECT statement, I get the error, "The select list for the INSERT statement contains fewer items than the insert list".

I would like to mention that TABLEB has an identity column as its key.

Also, if I run the above twice, the second time it is run, no additional rows are put in TABLEB. If I then delete the 3 rows starting with NAMECCC from TABLEB, and rerun the above, it inserts the 3 rows NAMECCC, NAMECCC, NAMECCC !

If I remove the WHERE clause, and run the above twice, then it puts 16 rows in TABLEB.

It's almost as if the SQL language is incapable of perceiving duplicates that have been added during the session that is running.

[ck9663]

I'm using SQL Express 2005.

I have two tables, TABLEA and TABLEB. TABLEA has the following:

col1
-----
NAMEAAA
NAMEAAA
NAMEBBB
NAMEBBB
NAMECCC
NAMECCC
NAMECCC
NAMEDDD

I want to insert rows into TABLEB so that:

fld1 fld2 fld3 fld4 fld5
---- ----- ---- ---- ----
NAMEAAA 0 NULL 0.0000 NULL
NAMEBBB 0 NULL 0.0000 NULL
NAMECCC 0 NULL 0.0000 NULL
NAMEDDD 0 NULL 0.0000 NULL
================================================== ======

If I try the following, 8 rows get created in TABLEB:

INSERT INTO TABLEB
([fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5])
SELECT col1,0,NULL,0,NULL
FROM TABLEA
WHERE not exists (select * from TABLEB
where TABLEB.fld1 = TABLEA.col1);

If instead I try the following, 8 rows still get created:

INSERT INTO TABLEB
([fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5])
SELECT col1,0,NULL,0,NULL
FROM TABLEA
WHERE col1 NOT IN (select fld1 FROM TABLEB);
================================================== =

If I put the word DISTINCT before "col1" in my SELECT statement, I get the error, "The text, ntext, or image data type cannot be selected as DISTINCT". If I remove the "col1,0,NULL,0,NULL" from the SELECT statement, I get the error, "The select list for the INSERT statement contains fewer items than the insert list".

I would like to mention that TABLEB has an identity column as its key.

Also, if I run the above twice, the second time it is run, no additional rows are put in TABLEB. If I then delete the 3 rows starting with NAMECCC from TABLEB, and rerun the above, it inserts the 3 rows NAMECCC, NAMECCC, NAMECCC !

If I remove the WHERE clause, and run the above twice, then it puts 16 rows in TABLEB.

It's almost as if the SQL language is incapable of perceiving duplicates that have been added during the session that is running.

What's the datatype of your COL1?

What I usually do in this kind of error is try and isolate the problem. For some experts, this maybe simple. But I'll share with you a technique am using.

First, try to successfully run your SELECT statement

SELECT DISTINCT col1,0,NULL,0,NULL
FROM TABLEA
WHERE TABLEA.col1 NOT IN (select TABLEB.fld1 FROM TABLEB)

If you can successfully ran that, it means the problem is not on your select statement.

Second, try to insert the result.

INSERT INTO TABLEB
([fld1],[fld2],[fld3],[fld4],[fld5])
SELECT DISTINCT col1,0,NULL,0,NULL
FROM TABLEA
WHERE TABLEA.col1 NOT IN (select TABLEB.fld1 FROM TABLEB)

If you have an error, that means it's not on your select, it's in your INSERT.

Now try and do a hardcoding of value:

NSERT INTO TABLEB
([fld1],[fld2],[fld3],[fld4],[fld5])
VALUES ('test',0,NULL,0,NULL)

If the insert fails, you have other problem than your query. Which means you need more detail analysis.

It's called isolation technique. I usually do this until I can perfect the command am using. For some INSERT T-SQL are simple, but for others, it could look complicated. So try this until you managed to perfect the syntax on whatever you're using.

Good luck.

-- CK

[HughManity]

I will play around with this technique. The data type in question is VARCHAR. I assumed the errors I am getting are normal - I brought them up to point out the inflexibility and inscrutability of the language and to reinforce the importance of finding a WHERE clause that would work, since my options to do it another way are apparently limited. After searching google for hours and finding the same simplistic (and apparently inaccurate) examples over and over, I did finally stumble on a clause that seems to do the trick as far as inserting only unique values (it isn't intuitively clear why it works, or what it even does). I replace my WHERE clause with the following:

group by col1
having count(*) > 0;