Select and Insert query. - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Select and Insert query.

Author

Topic

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-26 : 07:56:58

I'm assuming I need to run a stored procedure for what I'm trying to do. In dbo.tblNumber, I have a column name fldResult. Within fldResult, I have thousands of cells with data similar to this: 00-02.

I'm trying to substring the numbers and insert them into dbo.temp

For example: SELECT SUBSTRING ([fldResult], 1, 2)

After the numbers are inserted into my dbo.temp table, I would like to do another INSERT and GROUP BY into the table dbo.final for a final count.

Can somebody give me some advice on how to perform this query?
Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-26 : 17:47:37

So what did you try then?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 12:27:38

you want to group by what field? and also insert what all fields? with limted info provided, i dont think anybody can help you much. could you provide the reqd info?

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-27 : 14:20:58

quote:
Originally posted by sodeep

So what did you try then?

I tried this within a stored procedure:

INSERT INTO temp
SELECT SUBSTRING([fldResult], 1, 2),
SUBSTRING([fldResult], 4, 2)
FROM tblNumber

I thought this would pull out all the numbers such as:
00-02
00-03
00-03
01-02
....(there are about 500 more number)
and insert them into the table temp.

As a simple SELECT query, this works:
SELECT SUBSTRING([fldResult], 1, 2) AS DR1,
SUBSTRING([fldResult], 4, 2) AS DR2
FROM tblNumber

If I could do this, then all the numbers would be in one column. Once all the numbers are in one column, then I could run a GROUP BY query for a final count of each number.

dbo.final
[C1][C2]
00 3
02 2
03 2
01 1

Any suggestions?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-27 : 14:33:07

Your question is still not clear.You need to explain clearly as what you need.

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-27 : 22:44:56

How can I combine these numbers into one:

00-02
00-03
00-03
01-02

such as:

00
02
00
03
00
03
01
02

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-28 : 22:48:51

you could do it in two steps, something like this:

INSERT INTO temp SELECT SUBSTRING([fldResult], 1, 2) FROM tblNumber
INSERT INTO temp SELECT SUBSTRING([fldResult], 4, 2) FROM tblNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 00:57:19

[code]
SELECT PK,Val
FROM
(SELECT PK,LEFT(Col,2) AS Val,1 AS Ord FROM Table
UNION ALL
SELECT PK,RIGHT(col,2),2 FROM Tabl;e
)t
ORDER BY PK,Ord
[/code]

PK is tables primary key

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-29 : 09:02:13

quote:
Originally posted by sunitabeck

you could do it in two steps, something like this:

INSERT INTO temp SELECT SUBSTRING([fldResult], 1, 2) FROM tblNumber
INSERT INTO temp SELECT SUBSTRING([fldResult], 4, 2) FROM tblNumber

Thanks for the reply. I'm a .net programmer slowly trying improve my SQL programming so I can do a lot of processing on the back end.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:35:01

did you try my suggestion?

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-29 : 12:55:11

quote:
Originally posted by visakh16

did you try my suggestion?

Actually, I did the seperate INSERTS as shown above using Substring. This I ran this query:

SELECT NewNumber, COUNT(NewNumber) AS CNT
FROM temp
GROUP BY NewNumber

This worked perfectly.

I couldn't get your query to work visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 13:00:49

quote:
Originally posted by TheDirtyBird

quote:
Originally posted by visakh16

did you try my suggestion?

Actually, I did the seperate INSERTS as shown above using Substring. This I ran this query:

SELECT NewNumber, COUNT(NewNumber) AS CNT
FROM temp
GROUP BY NewNumber

This worked perfectly.

I couldn't get your query to work visakh16.

why? what was the error?

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-29 : 13:10:56

quote:
Originally posted by visakh16

SELECT PK,Val
FROM
(SELECT PK,LEFT(Col,2) AS Val,1 AS Ord FROM Table
UNION ALL
SELECT PK,RIGHT(col,2),2 FROM Tabl;e
)t
ORDER BY PK,Ord

PK is tables primary key

Well, there is no primary key and I don't know what Val is within your query. So I wasn't quite sure how to get your query to work with my data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 13:18:50

quote:
Originally posted by TheDirtyBird

quote:
Originally posted by visakh16

SELECT PK,Val
FROM
(SELECT PK,LEFT(Col,2) AS Val,1 AS Ord FROM Table
UNION ALL
SELECT PK,RIGHT(col,2),2 FROM Tabl;e
)t
ORDER BY PK,Ord

PK is tables primary key

Well, there is no primary key and I don't know what Val is within your query. So I wasn't quite sure how to get your query to work with my data.

Oh...no primary key in your table? why so?
Val is just alias name given to column we derived from yours.

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-29 : 17:14:44

quote:
Originally posted by TheDirtyBird

[quote]Originally posted by sunitabeck

you could do it in two steps, something like this:

INSERT INTO temp SELECT SUBSTRING([fldResult], 1, 2) FROM tblNumber
INSERT INTO temp SELECT SUBSTRING([fldResult], 4, 2) FROM tblNumber

When I run the queries, it works perfect. But, if I try to do it in a stored procedure, the INSERT statements don't work. Syntax is fine.

This is what I created:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIED ON
GO

CREATE PROCEDURE TopNumber
AS

DELETE FROM [dbo].[Temp]

INSERT INTO temp SELECT SUBSTRING([fldResult], 1, 2) FROM tblNumber

INSERT INTO temp SELECT SUBSTRING([fldResult], 1, 2) FROM tblNumber

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 17:15:42

Explain what you mean by "INSERT statements don't work".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-29 : 19:17:04

quote:
Originally posted by tkizer

Explain what you mean by "INSERT statements don't work".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Basically, my queries will not work within a stored procedure. If I run them independently one at a time, they work fine. I'm doing something wrong within my stored procedure.

My goal is to make one execution(stored procedure) instead of running the multiple INSERT statements (not using stored procedure). I know my example contains data such as:

01-02

but, if I have a number such as:

01-01-02-03-04-04

then a stored procedure would be best. That way I can do 6 INSERTS all at once.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 19:31:23

Your stored procedure as coded above is fine, so you must not be explaining it right. You still haven't explained what you mean by "will not work". Are you getting an error? Where are you running the stored procedure from? Your application or from a SQL client tool such as Management Studio? If from your application, have you tried running it in SSMS? EXEC dbo.StoredProcName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-29 : 20:57:34

quote:
Originally posted by tkizer

Your stored procedure as coded above is fine, so you must not be explaining it right. You still haven't explained what you mean by "will not work". Are you getting an error? Where are you running the stored procedure from? Your application or from a SQL client tool such as Management Studio? If from your application, have you tried running it in SSMS? EXEC dbo.StoredProcName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thank you for trying to help me and for your patience. I'm a .NET developer trying to improve my SQL skills. I took some screen shots to hopefully help explain what I am trying to accomplish.

I'm using SQL Server 2008 Management Studio.

I quickly threw together a test database. I have a 2 tables:
tblMain
temp

Within tblMain, I have 1 column:
fldResult

Within temp, I have 1 column:
Numbers

This is the data within my tblMain:
http://www.itchybyte.com/images/tblMain.jpg

I want to take all the numbers and INSERT them into the table temp so that I can do away with the delimiter (dash):
http://www.itchybyte.com/images/tblTempInsert.jpg

As you can see, it works:
http://www.itchybyte.com/images/selecttemp.jpg

After clearing table temp, I try again using a stored procedure.

But, when I try and run this within a stored procedure, the INSERT never happens (although the message states it was successful):
http://www.itchybyte.com/images/spTopNumber.jpg

My end result is to take all the numbers and count them using group by:
http://www.itchybyte.com/images/cnt.jpg

I am able to do by using multiple INSERT queries, but my goal is to create a stored procedure and execute them all in one trip.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-29 : 21:52:27

When you run the sql shown in http://www.itchybyte.com/images/spTopNumber.jpg , you are modifying the stored procedure. That only saves the stored procedure in the database for later use. It does not cause any of the sql statements specified in the stored procedure to be executed.

To execute the stored procedure and generate the results, you need to run the stored procedure using the "exec storedprocname" syntax. So in this case, you would execute this command:
exec TopNumber

After that, if you do a select from the Temp table, you will see the results.

TheDirtyBird
Starting Member

12 Posts

Posted - 2008-12-30 : 07:09:22

quote:
Originally posted by sunitabeck

When you run the sql shown in http://www.itchybyte.com/images/spTopNumber.jpg , you are modifying the stored procedure. That only saves the stored procedure in the database for later use. It does not cause any of the sql statements specified in the stored procedure to be executed.

To execute the stored procedure and generate the results, you need to run the stored procedure using the "exec storedprocname" syntax. So in this case, you would execute this command:
exec TopNumber

After that, if you do a select from the Temp table, you will see the results.

I did not know that ALTER PROCEDURE would not execute the sql statements. So, I need to DROP PROCEDURE and then CREATE PROCEDURE every time I run my procedure, correct?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources