Insert into one table, from two table without join

Question

I'm in Microsoft Sql Server I have three table.

The first table have just a field with ID_CONTACT.

CREATE TABLE USERS_CONTACT(
 [ID_CONTACT] [int] NOT NULL PRIMARY KEY
)

The second table have a field with UNIQUE_CODE

CREATE TABLE TMP_UNIC_CODE_RECETTE(
 [UNIC_CODE] varchar(10)
)

I have a third table with two field:

> ID_CONTACT, UNIC_CODE

CREATE TABLE UNIC_CODE_RECETTE(
 [ID_CONTACT] [int] NOT NULL PRIMARY KEY,
 [UNIC_CODE] varchar(10)
)

This two table haven't join. I need to push on the third table the ID_CONTACT and the UNIC_CODE randomly

I have try this:

INSERT INTO UNIC_CODE_RECETTE
(ID_CONTACT, UNIC_CODE)
SELECT ID, (SELECT REAC FROM TMP_UNIC_CODE_RECETTE )
FROM USERS_CONTACT

But it doesn't work.

Can you help me for the query ?

Thanks

"Doesn't work" means absolutely nothing to anybody else. We don't know what you are trying to do or what is not working the way you expect it to. How about posting the table structures, sample data and desired output. Here is a good place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Dec 14 '16 at 16:57

score 1 · Accepted Answer · edited May 23 '17 at 12:13

You can't return more than one value from a subquery the way you are trying to use it. You can use top 1 to do that though, and if you order by newid() it will give you an seemingly random value.

insert into unic_code_recette (id_contact, unic_code)
select 
    uc.id_contact
  , unic_code  = (
    select top 1 
        unic_code
      from tmp_unic_code_recette 
      order by newid()
      )
  from users_contact uc

Insert into one table, from two table without join

1 Answers1