Can I do this in SQL, too many rows in Excel!

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

Can I do this in SQL, too many rows in Excel!

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-18 : 07:59:05

Hi,

I can do this via VBA in Excel but I run out of rows (even in Excel 2010).

So, is it possible to run some sort of looping code that inserts a new row into a table on each loop for all values where R=1-(P1*S2)/(P2*S1) and where P1,P2,S1,S2 are integers between 20 and 70?

Advice greatly appreciated :-)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-18 : 08:10:28

Not only can you do it, SQL is at its best on this type of problems. What is even better, more likely than not, you may not have to loop - you may be able to do it all in one shot as a set-based update. If you can post your table DDL's along with some sample input data and desired output data, there are many people on this forum who would be able to tell you exactly what query would do that.

Take a look at Brett's blog if you need help getting DDL's etc. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-18 : 08:21:18

Thanks for the reply.

The empty table I have is simply five columns - namely, P1, P2, S1, S2 and R.

I would want the query to insert a new row for each and every value.

So row 1 would be 20, 20, 20, 20, the result of the formula
Row 2 would be 21, 20, 20, 20, the result of the formula

and so on......

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-18 : 08:54:20

This would be easiest to do using a numbers table. If you don't already have a numbers table, create one for now like this:

CREATE TABLE #Nums (n INT NOT NULL PRIMARY KEY CLUSTERED);
;WITH N(n) AS
( SELECT 20 UNION ALL SELECT n+1 FROM N WHERE N < 70 )
INSERT INTO #Nums SELECT n FROM N;

Then, you can use the numbers table to generate the data and insert using this query:

INSERT INTO 
	yourNewTable
SELECT
	n1.n AS P1,
	n2.n AS P2,
	n3.n AS S1,
	n4.n AS S2,
	1-(n1.n*n4.n)/(n2.n*n3.n)
FROM
	#Nums n1
	CROSS JOIN #Nums n2
	CROSS JOIN #Nums n3
	CROSS JOIN #Nums n4

However, that is 6,765,201 combinations - so it might take a while, and it might cause the log file to grow very large. If you have lots of disk space and a well-managed server, it may not be a problem - nonetheless, it might be best if you did this in a few chunks while watching the size of the log table.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-18 : 08:59:09

Thank you very much, I can only aspire to hopefully one day be able to produce such complex code so easily :-)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-18 : 12:48:03

You are quite welcome! Like anything else, it seems complex when you start out with SQL, but once you get into it it is fun.

One thing I missed in the query I posted earlier is that, in the calculation I was using integer division - which is probably not what you want. So you may want to change it to:


...	n4.n AS S2,
	1-(n1.n*n4.n*1.0)/(n2.n*n3.n)
FROM
...

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-18 : 15:15:02

Alas, I have tried your code.

It falls over because it doesn't like #Nums? Something about a # not being permissible?

Removing the # then results in an invalid syntax error message near "with"?

In the meantime, it also doesn't like "clustered"?

SQL Server 2008 used here if it's any use.

I assume the language has been written to deliberately trip up everyone whenever possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-19 : 03:53:32

you will need to include this part of the code for it to work


CREATE TABLE #Nums (n INT NOT NULL PRIMARY KEY CLUSTERED);
;WITH N(n) AS
( SELECT 20 UNION ALL SELECT n+1 FROM N WHERE N < 70 )
INSERT INTO #Nums SELECT n FROM N;

KH
[spoiler]Time is always against us[/spoiler]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-19 : 06:07:05

Yep, did that:

Major Error 0x80040E14, Minor Error 25556
> CREATE TABLE #Nums (n INT NOT NULL PRIMARY KEY CLUSTERED)
The number sign (#) is reserved and cannot be used as the first character of an identifier. [ Invalid identifier = #Nums ]

if I remove the #:

Major Error 0x80040E14, Minor Error 25501
> CREATE TABLE Nums (n INT NOT NULL PRIMARY KEY CLUSTERED)
There was an error parsing the query. [ Token line number = 1,Token line offset = 47,Token in error = CLUSTERED ]

if I remove CLUSTERED:

Major Error 0x80040E14, Minor Error 25501
> WITH N(n) AS
( SELECT 20 UNION ALL SELECT n+1 FROM N WHERE N < 70 )
INSERT INTO Nums SELECT n FROM N
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = WITH ]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-19 : 07:22:13

what database are you using ?

KH
[spoiler]Time is always against us[/spoiler]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-19 : 07:27:26

It's called TestDatabase#1.sdf

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-19 : 10:42:34

oh SQL Server compact edition, i think it does not support temp table and the create table syntax is a bit different
try this


CREATE TABLE Nums (n INT CONSTRAINT pk_n PRIMARY KEY)

KH
[spoiler]Time is always against us[/spoiler]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-19 : 11:54:12

Thanks, I'll try it when I get back onto my production machine.

I tried to install the SQL Express package onto this laptop and got this extremely helpful and informative error message:

"The following error has occurred:

An error occurred during the installation of assembly 'Microsoft.VC80.MFCLOC,version="8.0.50727.4027",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="amd64",type="win32"'. Please refer to Help and Support for more information. HRESULT: 0x80070091."

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-25 : 06:32:01

quote:
Originally posted by khtan

oh SQL Server compact edition, i think it does not support temp table and the create table syntax is a bit different
try this

CREATE TABLE Nums (n INT CONSTRAINT pk_n PRIMARY KEY)

Ok, the CREATE bit works but I still get the

There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = WITH ]

Am I correct in assuming, from reading this thread, that SQL Compact uses an entirely different language?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-25 : 07:35:34

it is still the same T-SQL just that i does not support full syntax and full feature as bigger brother SQL Server.

for you case,
1. create the Nums table
2. populate it with the values 20 to 70. You will only need as you are not using temp table.
3. the insert query


INSERT INTO 
	yourNewTable
SELECT
	n1.n AS P1,
	n2.n AS P2,
	n3.n AS S1,
	n4.n AS S2,
	1-(n1.n*n4.n)/(n2.n*n3.n)
FROM
	Nums n1
	CROSS JOIN Nums n2
	CROSS JOIN Nums n3
	CROSS JOIN Nums n4

KH
[spoiler]Time is always against us[/spoiler]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-25 : 08:56:57

Thanks for your assistance but I think I'm going to give the whole SQL approach a wide berth!

There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = INSERT ]

So the version I'm using doesn't even like INSERT? Does it actually do *anything* or is its lack of functionality the reason it's free?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-25 : 09:07:10

If you are running your application only on desktop, you can also consider Express edition which is also free. Express Edition is very much closer to Standard or Enterprise Edition. Compact Edition is actually a totally different class. When it as first release, it is meant for mobile devices. You can't really compare the functionality of Compact Edition of a full fletch SQL Server.

KH
[spoiler]Time is always against us[/spoiler]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-02-25 : 13:03:44

quote:
Originally posted by khtan

If you are running your application only on desktop, you can also consider Express edition which is also free. Express Edition is very much closer to Standard or Enterprise Edition. Compact Edition is actually a totally different class. When it as first release, it is meant for mobile devices. You can't really compare the functionality of Compact Edition of a full fletch SQL Server.

Thanks for the advice but I tried to install Express and, surprise surprise, got an error message:

"The following error has occurred:

An error occurred during the installation of assembly 'Microsoft.VC80.MFCLOC,version="8.0.50727.4027",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="amd64",type="win32"'. Please refer to Help and Support for more information. HRESULT: 0x80070091."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-26 : 02:49:52

i did a search on the web for the error message, this link pops up

http://forums.iis.net/p/1168719/1947176.aspx

see if that helps

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources