SQL (Server): Copy query result in existing table with structure of query result

Question

I have created an SQL query that merges duplicates in a table:

SELECT Min([XNbr]) AS [XNbr], [ZNbr], [Hamster], [Cat], [Cow], [Dog],
[Squirrel], [Bird], [Mouse], [Flower], [Tree], Max([TimeStamp]) AS
[TimeStamp]
FROM dbo.Sunshine
GROUP BY [ZNbr], [Hamster], [Cat], [Cow], [Dog], [Squirrel], [Bird],
[Mouse], [Flower], [Tree]
ORDER BY [XNbr]

I now want to extend this query to transfer the result in target table I created before that has the exact structure (same fields and types) of the query above. How can that be done?

To create the table on the fly use SELECT INTO. To insert into an existing table, use INSERT SELECT — Nick.Mc
– Nick.Mc, Commented May 30, 2017 at 8:42

Ilyes · Accepted Answer · 2017-05-30 08:19:56Z

0

You need just INSERT INTO :

INSERT INTO TargetTable (Columns)
SELECT Min([XNbr]) AS [XNbr], [ZNbr], [Hamster], [Cat], [Cow], [Dog],
[Squirrel], [Bird], [Mouse], [Flower], [Tree], Max([TimeStamp]) AS
[TimeStamp]
FROM dbo.Sunshine
GROUP BY [ZNbr], [Hamster], [Cat], [Cow], [Dog], [Squirrel], [Bird],
[Mouse], [Flower], [Tree]
ORDER BY [XNbr]

answered May 30, 2017 at 8:19

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomato32 · Accepted Answer · 2017-05-30 08:17:12Z

0

You can use Insert Into Select Syntax. Visit this link to refer more: https://www.w3schools.com/sql/sql_insert_into_select.asp

answered May 30, 2017 at 8:17

Tomato32

2,2531 gold badge12 silver badges11 bronze badges

Comments

JayaPrakash · Accepted Answer · 2017-05-30 08:25:16Z

Store the result temporarily in the table.

Use 'generate scrips' option in the sql server and chose the table created.

Generate script for both schema and data

http://www.c-sharpcorner.com/UploadFile/26b237/generate-database-script-in-sql-server-2012/

After generating the script, just change the table name.

Or

If the target table doesn't exist in the database then,

 SELECT Min([XNbr]) AS [XNbr], [ZNbr], [Hamster], [Cat], [Cow], [Dog], [Squirrel], [Bird], [Mouse], [Flower], [Tree], Max([TimeStamp]) AS [TimeStamp]                                     
 into TargetTable 
 FROM dbo.Sunshine 
 GROUP BY [ZNbr], [Hamster], [Cat], [Cow], [Dog], [Squirrel], [Bird], [Mouse], [Flower], [Tree] 
 ORDER BY [XNbr]

The generated table will have the same schema as the result.

Collectives™ on Stack Overflow

SQL (Server): Copy query result in existing table with structure of query result

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related