Multiple (vertical) line insert

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

Multiple (vertical) line insert

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 07:37:22

Hello there.

I want to create a multiple line insert by verticaly.

for example. I have

select

( Category, count(refNum)
where statement
group by category) as 'Jan'

( Category, count(refNum)
where statement
group by category) as 'Feb'

error received

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is there a method or a way i could get round this?

Regards

Rob

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 07:43:28

Are you trying to do a SELECT, or INSERT something into a table? If you are trying to select, what is the objective? To get the count for each category for each month perhaps? If so:

SELECT
	Category,
	DATENAME(MONTH, OrderDate) AS [Month],
	COUNT(refNum) AS RefnumCount
FROM
	YourTable
GROUP BY
	Category,
	DATENAME(MONTH, OrderDate)

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 07:44:34

can you give samples of your input data and expected output

------------------------
PS - Sorry my bad english

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 07:54:19

I want to insert into table from select statment

and to look like for example

category count (first insert) count (second insert)

cat1 12 12
cat2 56 56
cat3 32 45
cat4 65 98

so the data would not be inserted row by row.

i want it to insert column by column

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 07:58:15

What are the two numeric columns? It seems like it is the count of something. How do you calculate that count?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 08:01:29

yes thats all it is. A count of individuals under a certain category.

ie

select

( Category, count(refNum)
from table
where statement
group by category) as 'Jan'

( Category, count(refNum)
from table
where statement
group by category) as 'Feb'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 08:16:35

The way you have described it, wouldn't the counts in both columns be the same? What causes the count to be 32 in column 2 and 45 in column 3 for row 3 (cat3)? What you probably would need is something like this, but not clear to me what the "Something" that determines the counts in col2 and col3 are:

INSERT INTO YourDestinationTable
SELECT
	Category,
	COUNT(CASE WHEN Something = Col1Category THEN refNum END) AS Col1Count,
	COUNT(CASE WHEN Something = Col2Category THEN fefNum END) AS Col2Count
FROM
	YourSourceTable
GROUP BY
	Category;

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 08:20:28

the count has no relevance.

All i want to do is insert into a table one column at a time.

were as a normal insert is one row at a time. The count number could be anything. doesnt matter.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 08:23:17

You cannot insert data into a table one column at a time. When you insert, you will have to insert one (or more) rows, providing valid values for every column. There are exceptions - if a column is nullable, you don't have to provide a value, it will assume null. If a column has the identity property, you don't need to provide the value etc. Except for those exceptions, you must provide value for every column for the rows that you are inserting.

Once you do that if you want to change the values in specific columns you would use the UPDATE statement.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 08:25:59

maybe i will have to look at pivot and just change the headings around.

thank you tho.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-20 : 11:36:31

If you's like some assistance, perhaps you can restate your question and provide DML, DDL and expected output. Here are some links that can help you provide that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-20 : 12:47:53

thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources