Using WHERE with INSERT INTO

Question

Using WHERE with INSERT INTO

Jacob Pressures

SSCertifiable

Points: 5804
More actions
February 2, 2010 at 8:56 am

#144746

I guess this doesn't make logical sense and it doesn't appear to work either.
INSERT INTO dbo.StandardSubject (AssessmentSubjectID, [Name], Description, AdoptionYear, IsActive, [RowVersion])
VALUES (NULL, 'Science', 'Science', NULL, 1, NEWID())
WHERE [Name] NOT LIKE 'Scie%'
I assume this doesn't work because the WHERE clause works against each row rather than look at the table as a whole to find out whether or not a certain value exists.
The only work around I can think of is to do something like
INSERT INTO dbo.table (Column1, Column2, Column3)
SELECT Value1, Value2, Value3
FROM dbo.OtherTable O
JOIN dbo.table T
WHERE T.Name NOT LIKE 'Scie%'
In this example that probably would not work either. But to show you the whole concept. I'm trying to create a query that creates another query to be executed by other users. The original query and what I was trying to do looks like this:
DECLARE @CRLF AS CHAR(2)
SET @CRLF = CHAR(13) + CHAR(10)
SET NOCOUNT ON
SELECT 'BEGIN TRANSACTION' + @CRLF
UNION ALL
SELECT DISTINCT 'INSERT INTO dbo.StandardSubject (AssessmentSubjectID, [Name], Description, AdoptionYear, IsActive, [RowVersion])
VALUES (NULL, ''' + g.Subject + ''', ''' + g.Subject + ''', NULL, 1, NEWID()) WHERE Name NOT LIKE ''' + LTRIM(RTRIM(SUBSTRING(Subject,1,4))) + '%'''
FROM Standards.dbo.[gwb-ok] g
What I want to do is exclude those inserts that already have a corresponding subject already in the table. currently you have to look up the table and delete the queries out that have values already present. To ease this problem I thought I'd write a where clause. But It doesn't work in its current form. So i thought I'd post my problem and get comments. Is the INSERT SELECT the best or only way to go?
I realize that this pseudo code doesn't seem to exactly make sense either. I'm just trying to make sure I'm headed in the right direction.
Best wishes thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What are you trying to do? What are you trying to achieve?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

What about:

INSERT INTO dbo.StandardSubject (AssessmentSubjectID, [Name], Description, AdoptionYear, IsActive, [RowVersion])

SELECT *

FROM (

SELECT AssessmentSubjectID = NULL,

[Name] = 'Science',

Description = 'Science',

AdoptionYear = NULL,

IsActive = 1,

[RowVersion] = NEWID()

) AS Data

WHERE [Name] NOT LIKE 'Scie%'

Is this what you are trying to do?

-- Gianluca Sartori

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 3

I'm trying to create a query that creates another query to be executed by other users. The query I'm actually working with is at the bottom of the last post.

What i want to do is exclude all the subjects not found in the second table. (I can't get EXCEPT to work because Math could be spelled Math or Mathematics. Social Studies could be Social Studies or Social Science etc. Thus i feel i need a where clause.)

Here is the problem: I have to insert subjects into a table but sometimes the subjects are already present. Rather than delete the insert statements that I create based on that information, I'd rather make the insert statements automatically exclude themselves if they are not needed. (OR simply don't generate the insert statement at all.)

Table with subjects needed to be added

Standards.dbo.[gwb-ok]

Subject, Column, Column, etc

Mathematics

Science

English

Social Studies

Table which needs more subjects added but already has subject as well

SouthDearborn.dbo.StandardSubject

Subject, Column, Column, etc

Math

Science

French

The query must identify that Math or Mathematics is already in the second table here and no insert statement needs to be created.

I hope this explains the problem better.

Thanks for your help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 5

I gave you in that second post, what i needed. This table has numerous columns that are not intuitive. These databases have nothing in common except for the subjects which i need to extract from one table and put in another. There can be no duplicate subjects in the second table. Everything in the first table has to be placed into the second table.

Here is a slim down version of the data I'm working with.

--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..[#gwb-ok],'U') IS NOT NULL DROP TABLE [#gwb-ok]

--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#StandardSubject,'U') IS NOT NULL DROP TABLE #StandardSubject

--===== Create the test table with

CREATE TABLE [#gwb-ok] (Subject nvarchar(50) NOT NULL)

CREATE TABLE #StandardSubject (Name nvarchar(100) NOT NULL)

INSERT INTO [#gwb-ok] (Subject)

SELECT 'Science' UNION ALL

SELECT 'Language Arts' UNION ALL

SELECT 'Social Studies' UNION ALL

SELECT 'Mathematics'

INSERT INTO #StandardSubject (Name)

SELECT 'COMPUTER STANDARD' UNION ALL

SELECT 'DEVELOPMENTAL STAGES' UNION ALL

SELECT 'HANDWRITING' UNION ALL

SELECT 'LANGUAGE ARTS' UNION ALL

SELECT 'MATHEMATICS' UNION ALL

SELECT 'PE/HEALTH STANDARD' UNION ALL

SELECT 'MUSIC STANDARDS' UNION ALL

SELECT 'READING' UNION ALL

SELECT 'VISUAL ART STANDARD' UNION ALL

SELECT 'WORK/SOCIAL BEHAVIOR'

--===== Setup any special required conditions especially where dates are concerned SET DATEFORMAT DMY

-- The information in the second table changes. Sometimes the subjects are slightly different. We have to test for different spellings like Math instead of Mathematics, Social Science instead of Social Studies.

-- There can be no duplicate subjects in the second table.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Given your sample data, this appears to do what you need:

insert into GWB ([Subject])

select name

from #StandardSubject SS

left outer join [#gwb-ok] GWB

on GWB.[Subject] like left(SS.Name, 3) + '%'

where [Subject] is null;

From your first post, I gather that's not exactly what you're looking for, but I can't precisely tell why. If you can clarify that point, it should be possible to make whatever modifications are needed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 7

I think this might be it. Thanks! I'm going to give it a try. I didn't know i could use the LIKE operator in the Join...ON clause. I had thought of that but it just looked a bit silly to me.

The first post was based on where I was at the time using a dumb down version of a query i wrote months ago and wanted to improve upon.

Thanks! I'll check it out and get back to you.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 8

Fantastic! IT WORKS!

Now all i have to do is create a query that will generate this query which should be quite easy.

thanks!

GSquared SSC Guru Points: 260824 More actions · Answer 9

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon