Inserting data into table using select query

Ajay

14y1.7k

Hi

I have one table like
  Columsns

ID(Primary key)    AP        MP      TN     MP      ..... These are all state names as columns

my query returning the data like

Statename       Count
AP                     10
MP                     20
TN                      30

How can i insert  this result set in to table in sql server.

Please help

Regards
Ajay

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi14y ago
Hi Ajay,

you can solved your problem using PIVOT Query in SQL

For this you must have fix column means AP,MP, ect are fixed.

Now I am assume your table just like table created by Senthilkumar( in above post).

try....

SELECT *
FROM
(SELECT StateName, COUNT(*) AS [Count1] FROM States GROUP BY StateName ) ps
PIVOT
(
sum (Count1)
FOR StateName IN
( [AP], [MP],[TN])
) AS pvt

please refer
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

hope this help.
Senthilkumar14y ago
CREATE TABLE States
(
ID INT IDENTITY(1,1) PRIMARY KEY,
StateName VARCHAR(100)
)

INSERT INTO States(StateName) VALUES('TN')
INSERT INTO States(StateName) VALUES('MP')
INSERT INTO States(StateName) VALUES('AP')
INSERT INTO States(StateName) VALUES('TN')
INSERT INTO States(StateName) VALUES('MP')

SELECT StateName, COUNT(*) AS [Count] FROM States GROUP BY StateName

The result can be stored in the two way. One way is to create the another table to hold the query results. It will be stored permanently.

CREATE TABLE StatesCount
(
StateName VARCHAR(100),
StateCount INT
)
INSERT INTO StatesCount(StateName,StateCount)
SELECT StateName, COUNT(*) AS [Count] FROM States GROUP BY StateName

-- SELECT * FROM StatesCount

The another way is you can hold the results in the temp table. If it is local temp table then only available to the current session.
If it is global then available across all the session users in sql server.

SELECT StateName, COUNT(*) AS [Count] INTO #tmpStateCount FROM States GROUP BY StateName

--SELECT * FROM #tmpStateCount

If it is helps you then please mark as accepted answer.