SQL Question - how to put into table?

Question

how do i get this result into a new table?

SELECT DISTINCT h.CustomerCode, h.BillName, h.BillAddress1
FROM hist2 h
WHERE NOT EXISTS
       (SELECT CustomerCode FROM tblCustomer c WHERE c.CustomerCode = h.CustomerCode)

Answer 1

This works on most SQLs—I'm not sure of MS:

create table tablename
  select Distinct h.CustomerCode, h.BillName, h.BillAddress1
  From hist2 h
  where not exists (select CustomerCode
                     From tblCustomer c
                     Where c.CustomerCode=h.CustomerCode)

Answer 2

Like this:

SELECT DISTINCT h.CustomerCode, h.BillName, h.BillAddress1
INTO NewTable
FROM hist2 h
WHERE NOT EXISTS
   (SELECT CustomerCode FROM tblCustomer c WHERE c.CustomerCode = h.CustomerCode)

Answer 3

if a table exists that matches your fields:

insert into mytable
   select Distinct h.CustomerCode, h.BillName, h.BillAddress1 From hist2 h where not exists (select CustomerCode From tblCustomer c Where c.CustomerCode=h.CustomerCode)

if it doesn't match your fields, you have to specify fields, as in all inserts:

insert into mytable (customercode, billname...)
    select Distinct h.CustomerCode, h.BillName, h.BillAddress1 From hist2 h where not exists (select CustomerCode From tblCustomer c Where c.CustomerCode=h.CustomerCode)

if the table doesn't exist, you want to use Select Into

Answer 4

If you don't have a table already, e.g. you want to store it into a brand new table, you can use this syntax here:

SELECT DISTINCT h.CustomerCode, h.BillName, h.BillAddress1
INTO dbo.NewTable
FROM hist2 h
WHERE NOT EXISTS
       (SELECT CustomerCode FROM tblCustomer c WHERE c.CustomerCode = h.CustomerCode)

Just add the "INTO (tablename)" clause - that's all there is!