Insert data into temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert data into temp table

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-12 : 13:03:50

Hi All,
I have a query which returns 24 values for column ID in table Employee. I have created a temp table with 24 columns. Is there a way by which I can insert all the 24 values into the temp table?

Thanks in anticipation,
Mavericky

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-12 : 13:09:52

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

Did you really mean to name your table "Personnel" or do you have only one employee as you said? Is this vague "id" columns supposed to be "emp_id"?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-12 : 13:19:00

CREATE TABLE [dbo].[Employee](
[ID] [int] NOT NULL,
[Name] [varchar](50),
)
GO

--For column ID, there are 24 values in the table.
--I want to insert these values in the table below.

CREATE TABLE #tmpBus
( HE1 INT,
HE2 INT,
HE3 INT,
HE4 INT,
HE5 INT,
HE6 INT,
HE7 INT,
HE8 INT,
HE9 INT,
HE10 INT,
HE11 INT,
HE12 INT,
HE13 INT,
HE14 INT,
HE15 INT,
HE16 INT,
HE17 INT,
HE18 INT,
HE19 INT,
HE20 INT,
HE21 INT,
HE22 INT,
HE23 INT,
HE24 INT);

INSERT INTO #tmpBus (HE1, HE2, HE3, HE4, HE5, HE6, HE7, HE8, HE9, HE10, HE11, HE12, HE13, HE14, HE15, HE16, HE17, HE18, HE19, HE20, HE21, HE22, HE23, HE24)

Thanks,
Mavericky

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-12 : 13:19:29

Personnel is plural. We cannot read your mind. What do you mean by "might might". Saying "can not read minds" implies that you have the ability, but you choose not to read minds. Please read a book on standard English. Also, read an entire dictionary, preferably the Oxford English Dictionary. Stop writing sentences until you learn standard English.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-12 : 13:45:52

You can do this by pivoting, as in the example below. You have to be on SQL 2005 or later for this to work.


;WITH cte AS
(
	SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RN
	FROM dbo.Employee
)
INSERT INTO #tmpBus
SELECT
	*
FROM
	cte
PIVOT
( MAX(Id) FOR RN IN
	(
		[1],[2],[3],[4],[5],[6],[7],,[9],[10],
		[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
		[21],[22],[23],[24]
	)
)P

That eighth ball that you see in the posting is really the number 8 between a left square bracket and a right square bracket.

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-12 : 14:51:49

thanks a lot sunitabeck!! Works like a charm :)

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-12 : 15:56:49

Employee table IDs :
180.000000000
180.000000000
180.000000000
270.000000000
270.000000000
270.000000000
360.000000000
360.000000000
360.000000000
450.000000000
450.000000000
450.000000000
540.000000000
540.000000000
540.000000000
630.000000000
630.000000000
630.000000000
720.000000000
720.000000000
720.000000000
810.000000000
810.000000000
810.000000000

And after pivoting i get in #tmpBus:
180,180,180,190,190,190,200,200,200,270,270,270,285,285,285,300,300,300,360,360,360,380,380,380

Is there a way i can get the original IDs horizontally?

Mavericky

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-12 : 17:21:34

My problem is solved. No reply is needed.

Thanks,
Mavericky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources