CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Luka
Luka

Reputation: 189

Insert into select from SQL Server

In table1 is some changes opposite table2. I need get in to table3 only values which is missing in table1 opposite table2.

Table1

Column1
a
b

d
e

g
h
i

Table2

Column1
a
b
c
d
e
f

Code:

INSERT INTO [dbo].[table3]
    SELECT *
    FROM [dbo].[table2]
    WHERE NOT EXISTS (SELECT *
                      FROM [dbo].[table1]
                      WHERE [dbo].[table2].column1 = [dbo].[table1].column1 
                        AND [dbo].[table2].column1 = [dbo].[table1].Pernr);

I get an error:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

I need :

Table3

Column1
c
f

Yesterday my script work but today is going wrong.. I do not know why?

Thanks for opinion

Upvotes: 1

Views: 6997

Answers (3)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use just "not in" - sub query as below

select * into #yourTable3 from #yourtable2 
    where column1 not in (select column1 from #yourtable1 where column1 is not null)

Your tables :

create table #yourtable1 (column1 varchar(10) )
insert into #yourtable1 (
Column1 ) values
 ('a')
,('b')
,(null )
,('d')
,('e')
, ( null )
,('g')
,('h')
,('i')

create table #yourtable2 (column1 varchar(10))
insert into #yourtable2 (column1 ) values
 ('a')
,('b')
,('c')
,('d')
,('e')
,('f')

Upvotes: 1

JonWay
JonWay

Reputation: 1735

DECLARE @Table1 TABLE (Column1 VARCHAR(2))
INSERT INTO @Table1  VALUES ('a'),('b'),('d'),('e'),('g'),('h'),('i')

DECLARE @Table2 TABLE (Column1 VARCHAR(2))
INSERT INTO @Table2  VALUES ('a'),('b'),('c'),('d'),('e'),('f')


DECLARE @Table3 TABLE (Column1 VARCHAR(2))
INSERT INTO @Table3 
SELECT 
*
FROM  @Table2 T2
WHERE T2.Column1 NOT IN
(SELECT Column1 FROM @Table1)

SELECT
* 
FROM @Table3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

When you use insert list the columns:

INSERT INTO [dbo].[table3](column1)
    SELECT t2.column1
    FROM [dbo].[table2] t2
    WHERE NOT EXISTS (SELECT 1
                      FROM [dbo].[table1] t1
                      WHERE t2.column1 = t1.column1
                     );

Of course, you can have more than one column for the insert. Just list all of them in both the insert and the select.

I don't know what Pernr. It is not part of your question. You can, of course, add additional clauses to the WHERE clause in the subquery.

If table3 does not exist, then use select into rather than insert:

    SELECT t2.*
    INTO table3
    FROM [dbo].[table2] t2
    WHERE NOT EXISTS (SELECT 1
                      FROM [dbo].[table1] t1
                      WHERE t2.column1 = t1.column1
                     );

Upvotes: 4

Related Questions