SQL Server Select into Existing Table

SQL Server SELECT into existing table

SELECT ... INTO ... only works if the table specified in the INTO clause does not exist - otherwise, you have to use:

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

SQL 'insert into select' into existing table

insert into Worker(WorkingPlace) 
    select WorkingPlace 
    from source.Worker;

This will create as many new rows in table Worker as there are rows returned from the select.

So if your query returns 10 rows, then 10 new rows will be inserted into table Worker

This means that any column in that table that is defined as not null must receive a value, either from your query, or get a default value when that is defined.

suppose your table looks like this

create table Worker (id int not null, WorkingPlace varchar(50) null, SomeValue int not null)

then you cannot insert a row like this

insert into Worker (WorkingPlace) values ('something')

This will fail because you have not provided a value for then column id that is defined as not null, and no value for the column SomeValue because that is also defined as not null

if your table looks like

create table Worker (id int primary key not null identity, WorkingPlace varchar(50) null, SomeValue int not null)

then the statement

insert into Worker (WorkingPlace) values ('something')

Will only fail because there is no value for the column SomeValue.

The column id will get its value from the identity

Solution:

insert into Worker (WorkingPlace, SomeValue) values ('something', 123)

select * into table Will it work for inserting data into existing table

You should try

INSERT INTO ExistingTable (Columns,..)
SELECT Columns,...
FROM OtherTable

Have a look at INSERT

and SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

How to insert new records into existing table in SQL?

Based on the assumption, that customer, street and zipcode form a unique key you can do it like this:

insert into existing_table (customer, street, zipcode)
select customer, street, zipcode -- other columns
from query as new
where not exists (select null from existing_table as old where old.customer = new.customer and old.street = new.street and old.zipcode = new.zipcode)

So you're only inserting value that are not already there according to the unique key.

How to insert into an existing temp table in SQL Server

Change it into a insert into statement. Otherwise you create the same temp table multiple times and that is not allowed.

Insert into #testingTemp (a,b,c)
Select a.ID AS [a], 
       b.ID AS [b], 
       c.ID AS [c]
FROM

t sql select into existing table new column

In an INSERT statement you cannot reference the table you are inserting into. An insert works under the assumption that a new row is to be created. That means there is no existing row that could be referenced.

The functionality you are looking for is provided by the UPDATE statement:

UPDATE t1
SET MonthTot = t2.MonthTot 
FROM #temptable1 t1
JOIN #temptable2 t2
ON t1.Customer = t2.Customer;

Be aware however, that this logic requires the Customer column in t2 to be unique. If you have duplicate values in that table the query will seem to run fine, however you will end up with randomly changing results.

For more details on how to combine two tables in an UPDATE or DELETE check out my A Join A Day - UPDATE & DELETE post.

SQL Server 'select * into' versus 'insert into ..select *

The select * into table1 from table2 where 1=1 creates table1 and inserts the values of table2 in them. So, if the table is already created that statement would give an error.

The insert into table1 select * from table2 only inserts the values of table2 in table1.

SQL: select insert into select + add fixed value fron another table

Asuming table3 doesn't exist yet:

SELECT t1.id, t2.[timestamp], t2.column1, t2.column2 INTO table03
FROM table02 t2 INNER JOIN table01 t1 ON t2.[timestamp] BETWEEN t1.timestamp_begin AND t1.timestamp_end

If table3 already exists

INSERT INTO table03 SELECT t1.id, t2.[timestamp], t2.column1, t2.column2 
FROM table02 t2 INNER JOIN table01 t1 ON t2.[timestamp] BETWEEN t1.timestamp_begin AND t1.timestamp_end

As @Caius Jard suggested, you could have begun by creating the select to get the records you wanted to insert, and later modifying it with the select into / insert into syntax.

How do insert data into a table that already exists?

A simplified test to showcase methods to insert.

CREATE TABLE TableA (
ID INT GENERATED ALWAYS AS IDENTITY,
ColA1 INT, 
ColA2 VARCHAR(30)
);

--
-- INSERT VALUES into existing table
--
INSERT INTO TableA (ColA1, ColA2) VALUES
(10, 'A'),
(20, 'B'), 
(30, 'C');

3 rows affected

--
-- SELECT INTO new table
--
SELECT ID, ColA1+2 AS ColB1, ColA2||'2' AS ColB2 
INTO TableB
FROM TableA;

3 rows affected

--
-- INSERT from SELECT with explicit columns
--
INSERT INTO TableA (ColA1, ColA2) 
SELECT ColB1+1, CONCAT(LEFT(ColB2,1),'3') AS ColB23
FROM TableB;

3 rows affected

SELECT * FROM TableA;

id | cola1 | cola2
-: | ----: | :----
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | C    
 4 |    13 | A3   
 5 |    23 | B3   
 6 |    33 | C3   

--
-- INSERT from SELECT without columns
-- Only works when they have the same number of columns. 
--
INSERT INTO TableB
SELECT *
FROM TableA;

6 rows affected

SELECT * FROM TableB;

id | colb1 | colb2
-: | ----: | :----
 1 |    12 | A2   
 2 |    22 | B2   
 3 |    32 | C2   
 1 |    10 | A    
 2 |    20 | B    
 3 |    30 | C    
 4 |    13 | A3   
 5 |    23 | B3   
 6 |    33 | C3   

db<>fiddle here

SQL insert into existing table based on condition

You can assign a boolean:

ALTER TABLE vault ADD COLUMN is_master BOOLEAN NOT NULL;

UPDATE value
    SET is_master = (name = 'Master');

EDIT:

To handle NULL values for master:

UPDATE value
    SET is_master = (name IS NOT DISTINCT FROM 'Master');

---

Related Topics