SQL Server SELECT into existing table

Question

I am trying to select some fields from one table and insert them into an existing table from a stored procedure  Here is what I am trying   SELECT col1  col2 INTO dbo TableTwo  FROM dbo TableOne  WHERE col3 LIKE  search key   I think SELECT     INTO     is for temporary tables which is why I get an error that dbo TableTwo already exists   How can I insert multiple rows from dbo TableOne into dbo TableTwo

User · Answer

select   into existing table database  existingtable from database  othertables       If you have used select   into tablename from other tablenames already  next time  to append  you say select   into existing table tablename from other tablenames

User · Answer

There are two different ways to implement inserting data from one table to another table   For Existing Table - INSERT INTO SELECT  This method is used when the table is already created in the database earlier and the data is to be inserted into this table from another table  If columns listed in insert clause and select clause are same  they are not required to list them  It is good practice to always list them for readability and scalability purpose   ----Create testable CREATE TABLE TestTable  FirstName VARCHAR 100   LastName VARCHAR 100   ----INSERT INTO TestTable using SELECT INSERT INTO TestTable  FirstName  LastName  SELECT FirstName  LastName FROM Person Contact WHERE EmailPromotion   2 ----Verify that Data in TestTable SELECT FirstName  LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable   For Non-Existing Table - SELECT INTO  This method is used when the table is not created earlier and needs to be created when data from one table is to be inserted into the newly created table from another table  The new table is created with the same data types as selected columns   ----Create a new table and insert into table using SELECT INSERT SELECT FirstName  LastName INTO TestTable FROM Person Contact WHERE EmailPromotion   2 ----Verify that Data in TestTable SELECT FirstName  LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable   Ref 1 2

User · Answer

If the destination table does exist but you don t want to specify column names   DECLARE  COLUMN LIST NVARCHAR MAX   DECLARE  SQL INSERT NVARCHAR MAX    SET  COLUMN LIST    SELECT DISTINCT     SUBSTRING                        SELECT    table1     SYSCOL1 name  AS  text                FROM sys columns SYSCOL1             WHERE SYSCOL1 object id   SYSCOL2 object id and SYSCOL1 is identity  lt  gt  1             ORDER BY SYSCOL1 object id             FOR XML PATH                 2  1000  FROM     sys columns SYSCOL2 WHERE     SYSCOL2 object id   object id  dbo TableOne      SET  SQL INSERT     INSERT INTO dbo TableTwo SELECT      COLUMN LIST     FROM dbo TableOne table1 WHERE col3 LIKE      search key EXEC sp executesql  SQL INSERT

User · Answer

It would work as given below    insert into Gengl Del Select Tdate DocNo Book GlCode OpGlcode Amt Narration  from Gengl where BOOK     amp  lblBook Caption  amp     AND DocNO    amp  txtVno Text  amp

User · Answer

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] SQL Server SELECT into existing table

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures