It is currently Wed Apr 23, 2014 9:51 pm




Post new topic Reply to topic  [ 7 posts ] 
 Inserting data in a tall format 
Author Message
General
General

Joined: Tue Mar 24, 2009 9:14 am
Posts: 785
Location: Hudson, MI
Post Inserting data in a tall format
Anyone ever see/use thisINSERT/UNION ALL method?

Still a bit of an SQL noob, so it may not be anything new here. The nice thing is that you can use this in a single Expression Item (or Action Item, if you're legacy).

Also note that SQL Server 2008 is suuposed to make more efficient use Insert transactions and shiny new structures, but for 2000 (which is what I got saddled with), this makes life a bit easier.

_________________
Jordan

Duct tape is like The Force. It has a light side, a dark side, and it holds the universe together.


Thu Nov 11, 2010 1:18 pm
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: Inserting data in a tall format
One thing to look out for using that INSERT SELECT method is that you cannot select information from the same table you are inserting into.

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Mon Nov 22, 2010 11:12 am
Profile WWW
General
General

Joined: Tue Mar 24, 2009 9:14 am
Posts: 785
Location: Hudson, MI
Post Re: Inserting data in a tall format
Travis.Cox wrote:
One thing to look out for using that INSERT SELECT method is that you cannot select information from the same table you are inserting into.


Ok, I may very well regret asking this, but why would you want to? I only say that because it seems cicurlar. Kind of like using an Excel formula that points to itself. :scratch:

Actually I was thinking in terms of how a transaction group works. Normally it would store things in a wide format. Maiking the items read only and referencing them in an INSERT SELECT method gives you option of storing in a tall format.

_________________
Jordan

Duct tape is like The Force. It has a light side, a dark side, and it holds the universe together.


Wed Nov 24, 2010 8:58 am
Profile
General
General

Joined: Mon Sep 29, 2008 11:38 am
Posts: 302
Post Re: Inserting data in a tall format
I can provide an example of why -

I have a rather wide table that has 3 separate insert queries associated with it(Fix32 had a limit to the number of items per SQD block). My coworker was trying to use this basically to combine each 'set' of 3 rows into 1 coherent row.


Wed Nov 24, 2010 9:36 am
Profile
General
General

Joined: Tue Mar 24, 2009 9:14 am
Posts: 785
Location: Hudson, MI
Post Re: Inserting data in a tall format
Thanks for replying, Dravik.

Dravik wrote:
[snip]... My coworker was trying to use this basically to combine each 'set' of 3 rows into 1 coherent row.

Meaning that he was taking three rows out of a table and inserting it as one row in the same table? I'm really not trying to be dense, I'm just trying to visualize this.

_________________
Jordan

Duct tape is like The Force. It has a light side, a dark side, and it holds the universe together.


Wed Nov 24, 2010 10:22 am
Profile
General
General

Joined: Mon Sep 29, 2008 11:38 am
Posts: 302
Post Re: Inserting data in a tall format
Sure, he had a table that looked like this


*,*,*,1,2,3,*,*,*,2010-01-01 00:00:00
4,5,6,*,*,*,*,*,*,2010-01-01 00:00:00
*,*,*,*,*,*,7,8,9,2010-01-01 00:00:00

and he wanted to combine those rows into one like this

4,5,6,1,2,3,7,8,9,2010-01-01 00:00:00


We're doing it w/ a temp table right now, but you can see where the idea of doing an insert select from the same table would have been cool :)


Wed Nov 24, 2010 10:43 am
Profile
Moderator
Moderator

Joined: Wed Apr 18, 2007 11:00 am
Posts: 645
Location: Sacramento Ca.
Post Re: Inserting data in a tall format
Dravik wrote:
Sure, he had a table that looked like this


*,*,*,1,2,3,*,*,*,2010-01-01 00:00:00
4,5,6,*,*,*,*,*,*,2010-01-01 00:00:00
*,*,*,*,*,*,7,8,9,2010-01-01 00:00:00

and he wanted to combine those rows into one like this

4,5,6,1,2,3,7,8,9,2010-01-01 00:00:00


We're doing it w/ a temp table right now, but you can see where the idea of doing an insert select from the same table would have been cool :)


I did something like this recently. It's a pain to do it in SQL, so I added a RawData property to the table with the SQL query to get the raw set. ie:
Code:
*,*,*,1,2,3,*,*,*,2010-01-01 00:00:00
4,5,6,*,*,*,*,*,*,2010-01-01 00:00:00
*,*,*,*,*,*,7,8,9,2010-01-01 00:00:00
And I added a propertyChange script that massaged the raw data into the new set and put it in the 'data' property of the table.

This is not elegant, but it works. One thing you have to look out for is that allowing the user to update this new row will be more work than usual.

_________________
Robert McKenzie
Inductive Automation
Technical Support


Wed Nov 24, 2010 11:26 am
Profile WWW
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: