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 INTO help

Author  Topic 

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-14 : 23:00:12
I am creating a new table from two different tables that will be a stored procedure. I am using Insert Into to create the table (I will eventually delete data and just pick the most recent 50 records each time the procedure is triggered).

SELECT a.resultsID, a.result.Time, b.compoundName, b.concentration
INTO resultsCombined
FROM a
INNER JOIN b
ON a.resultID=b.resultID

My issue is that for each resultID there is 3 compoundName s and 3 concentrations and I want both of those in one record based on the resultID. The result.Time is the same for each resultID.

1 09142011 09:53:00 Ca .0427
1 09142011 09:53:00 Cl .0379
1 09142011 09:53:00 Al .0797
2 09142011 09:57:03 Ca .0432
2 09142011 09:57:03 Cl .0234
2 09142011 09:57:03 Al .0765

AND I WANT: (With the compound name as the column name)

1 09142011 09:53:00 .0427 .0379 .0797
2 09142011 09:57:03 .0432 .0234 .0765

Please help me get there

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 00:17:51
something like

SELECT resultsID,Time,[Ca],[Cl],[Al]
FROM table
PIVOT (SUM(concentration) FOR compoundName IN ([Ca],[Cl],[Al]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-15 : 17:30:17
This may work, but does anyone else have any suggestions?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 17:31:18
What is wrong with what visakh posted?

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

Subscribe to my blog
Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-15 : 17:48:37
Nothing is wrong. Haven't had a chance to try yet. I just like to get a few different ideas on how to approach the same problem. Gives me and others reading the solution some alternatives.
Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-15 : 23:38:04
Thanks for the reply visakh16, I really wasn't aware of the pivot and was thinking of the pivot table in Excel. Good read on it [url]http://msdn.microsoft.com/en-us/library/ms177410.aspx[/url] if anyone else is interested.

I do have another question on this though...
So I have my NewTable and it is on a sql 2008 box in a database named Lab. I have a link set up on a sql 2005 box on the same network called sqllink\sqlinstance. Object is to make this a stored procedure that I guess runs on the server 2005 box???? And I need it to only return new records (that it hasn't already put in the NewTable).

Sorry to be requesting so much help, but a little over my head at this point. Hopefully I can help others someday.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-16 : 11:57:03
why do you want to create remote procedure? Any specific reasons why you cant do in 2008 box itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 12:32:35
Named "Lab"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 12:33:27
quote:
Originally posted by chapm4

I am creating a new table from two different tables



I think these dear folks deserve an answer to WHY?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-16 : 16:20:14
quote:
Originally posted by visakh16

why do you want to create remote procedure? Any specific reasons why you cant do in 2008 box itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





The database resides on a sql 2008 box which is on one network. There is another sql 2005 box which is on that network and also on a PLC network (two nics). The company wants to push this lab data to a PLC for reporting. I set up a linkedserver from the 2008 box to the 2005 box. I need a stored procedure on the 2005 box that the PLC program will trigger to pick up the last....let's say 20 records to push to the PLC. The reason I asked about combining the two tables into one is the Lab data is pushed to the sql 2008 box into two tables (that's the way the xray machine in the lab pushes it out to a Access App and then it pushes it to SQL. I am certainly open to a "better" way of doing things, but the ultimate goal is to link to that Lab database and have a stored procedure on the other server to be triggered by the the PLC.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 21:02:40
ahhh...see the original intent is always the best way to start.

Do you have DDL?

Can you set up a delta process?

Do you have a period of time that you want to do this"

Do you want a trigger to fire on ever DML event?

Or can we rely on Harry?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-16 : 21:06:39
ahhh..also..and this is from a pub with wifi...but most recent transactions mean to me a batch window...you have to close the window and process, then open the window..for the next batch..hey...I wasn't finished with that...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-20 : 14:30:35
Sorry X002548, don't have a clue what you are talking about. Is the remote link not the way to go?
Go to Top of Page
   

- Advertisement -