CODEWITHSUNDEEP
sqlsql-servert-sqlselectsql-insert
DevDevOps
DevDevOps

Reputation: 15

Transfer from one table to another

I have a two tables called code and codeTemporary.

In the code table I will use

select Code from codeTemporary with (nolock)
EXCEPT
select Code from code with (nolock)

This difference is about 10 million.

The codeTemporary table has "Id(guid)","Code(string)","boxId(guid)"

The code table will have same Id,getdate,getdate,default guid,default guid, same Code, same boxId, 1.

insert into Code
select Id,GETDATE(),GETDATE(),'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',
(
select Code from code with(nolock)
EXCEPT
select Code from codeTemporary with(nolock)
) as Code
,boxId,1 
from codeTemporary with(nolock)
where boxId='xxxxxx (guid)'

But that code didn't work:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I transfer missing codes from codeTemporary to code?

Upvotes: 0

Views: 52

Answers (2)

GMB
GMB

Reputation: 222462

I would phrase this as:

insert into code
select
    id,
    getdate(),
    getdate(),
    '00000000-0000-0000-0000-000000000000',
    '00000000-0000-0000-0000-000000000000',
    code
from codeTemporary ct
where 
    boxId = 'xxxxxx (guid)'
    and not exists (select 1 from code c where c.code = ct.code)

Note that it is a good practice to enumerate the columns for insert, like:

 insert into code(col1, col2, col3, ...)
 select ...

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you want:

insert into Code ( . . . ) -- list the columns here
    select Id, GETDATE(), GETDATE(),
          '00000000-0000-0000-0000-000000000000',
          '00000000-0000-0000-0000-000000000000',
          tc.code
    from codeTemporary ct
    where not exists (select 1 
                      from code c
                      where c.code = ct.code
                     ) and
          ct.boxId ='xxxxxx (guid)'

Upvotes: 2

Related Questions