SQL Command Help requested

I was wondering, I dont know if this is off topic or not but thought I would ask because there has been lots of reference to SCADA systems. This question is more based on the SQL commands. 2005 SQL. I am currrently taking some night classes to bring myself upto speed but have not gotten to the advanced stuff yet and that is where I need assistance.

What I have are two tables. They have the same number of row but a different number of columns. What I want to do is copy 5 of the columns from table a to table b with the following exception. If a column in table b which is not copied from table a has a value of 1 dont copy any data to that row and set another column in that same row to a 1. If the value in this same referenced column has a 0 in it then copy the data from table a to table b. I want this set of commands to execute as fast as possible. The way in which i currently have it written it takes about 1 min which is way to long. I know there has to be a faster more efficient way and when I get to that part in my education I am sure I will find it but I have to get an answer quickly to finish up the reports section on my scada project at work. Any assistance greatly appreciated. like I said I know what I want to do and I can do it with basic commands but have not learned the advanced stuff yet.

Just so you know the copy will do 5 columns by 99 rows. Hope this helps. Have a Happy New Year.

mrtweaver said:

If a column in table b which is not copied from table a has a value of 1 dont copy any data to that row and set another column in that same row to a 1. If the value in this same referenced column has a 0 in it then copy the data from table a to table b.

Click to expand...

I don't understand this part, can you give any example, what are you trying to achieve?

As I mentioned there will be two table. Call them A and B. Table A have 7 columns and 99 rows, table B has 9 columns and 99 rows. There are 5 columns in each table that share the same header name. Two of the columns in table B are going to be bits,we will refer to them as bit-1 and bit-2, they will either be a 1 or a zero. During the copy process of the 5 matching columns from table A to table B, the bit-1 column from table B for that row will be tested. If this bit-1 column is a zero it will allow the trasfer of the 5 columns of data for that row to be updated. If however that bit-1 column is a 1 it will not allow the data from that row to be updated and it will set the bit-2 column for that row to a 1. Hopefully this helps.

Pandiani said:

I don't understand this part, can you give any example, what are you trying to achive?

Click to expand...

Can you post the SQL statement that you´ve already created?

I could but not until wednesday. My full project is at work. I thought I brought it home on my flash card but the version I have on my flash card is not the updated version with the commands I currently have. SO I have to wait till New years holiday time is over and we are back to work then I would be able to post it.

Some of what I can recall is:

Update emp1name from empdata (select x.emp1name =

This is all I can recall for right now. It is somewhat close but probably not to acurate. I have been doing some google research and found a command that may work but I am unfamiliar with it. It is new to 2005 SQL it is something in the TRY/CATCH area. I am still reading it but it might be what I am looking for. It is just understanding the command and its uses. Like I said i just started taking the classes to better myself. So I am still relatively a greenhorn. If it was not for the guys at Inductive Automation and our SQL programmer who has since left the company I would not be as far as I am with the project. I can say that in one instance I had done some commands in SQL and it was an update to a table and the way in which I done it took like 2 min to complete, our programmer finally had some time and looked at it and showed me a much better way, more advanced way, Now it only takes 4 seconds. Its all a learning curve. And the reason i am asking in this forum is like I said, 1. I am very new to this. 2. Our programmer we had left. 3. I want the knowledge and the list I am sure goes on but I can not think of anything else. So thanks to all whom read this and especially to all whom have the knowledge and are willing to share it. I can only hope to have that same type of knowledge to pass along someday as all have on this site.

Marshy said:

Can you post the SQL statement that you´ve already created?

Click to expand...

Martin, is this related to the queston you emailed me about? Check your voicemail at work too, i left you a message and emailed you a possible solution to your other question

Without being at work I can say for sure. I have had two serious type questions so I am unsure if I asked this one in a PM to you or not. As soon as I am back at work I will check my mail box and see what I did and did not send you. This way I will know for sure. As for calling if you are available this week, being a short week, I think I might finally see light at the end of the tunnel, unless of course it is an oncomming train. But let me know just in case I can persuade MGMT to let me have some private time and then I can contact you. Have a happy and prosperous New Year.

ghettofreeryder said:

Martin, is this related to the queston you emailed me about? Check your voicemail at work too, i left you a message and emailed you a possible solution to your other question

Click to expand...

View vs. Table

Well last day of holiday before going back to work. Thank god I am getting bored at home with the honey do list. Anyway while I was combing thru my notes I came across something and thought I would ask.


What exactly is the difference between a View and a Table in SQL?

Can Inductive Automation work with both?

Do you have to add in any extra commands in Inductive Automation to tell it that it is a view and not a table?

Thanks and HAPPY NEW YEAR!!!!!

i don't quite understand but to see some records you could try SELECT command:

SELECT * FROM TABLE_A

or to how only selected columns:

SELECT column1,column2,column4,column7 FROM Table_A

you can use condition to only show records with particular value in some column (such as 100 in column2 for example):

SELECT column1,column2,column4,column7 FROM Table_A WHERE column2=100

also lookup things like SELECT INTO which allows copying records from one table into another etc.

SELECT column1,column2,column4,column7 INTO Table_B FROM Table_A WHERE column2=100

A view is basically a saved SQL query, whereas tables are objects that store data. Views are typically used for complex queries such as JOINs from multiple tables or a SELECT query that has a lot of conditions.

Suppose we have a table things that contains 1000 rows of the columns: id, name, type, and description. I might then create a view called nathansFavoriteThings that returns 20 rows based on in-con-ceivably complex conditions that I've spent a lifetime optimizing.

You, as a user, would query against my view like a table:
SELECT * FROM [nathansFavoriteThings] WHERE type='food'

A few reasons you might want to use a view:
1. Hide the complexity and modify your query in fewer places
2. Use it as a "security tool"
3. "Flatten" out multiple tables

I'm pretty sure that FactoryPMI sees views as tables when query browsing. You can certainly type your own queries that include views - it just passes that on to the SQL database. I would expect this to be the case of any SCADA package that runs on SQL Server. I don't think views make sense for FactorySQL although they would work in an "Action Item" query.

Views are mostly about reading data from the SQL database. The writing analog would be prepared statements, which are precompiled queries, usually INSERT or UPDATE, that accept parameters. Prepared statements are usually only necessary (significantly advantageous) for very specialized applications. FactorySQL supports those as well.

mrtweaver said:

What exactly is the difference between a View and a Table in SQL?

Can Inductive Automation work with both?

Do you have to add in any extra commands in Inductive Automation to tell it that it is a view and not a table?

Thanks and HAPPY NEW YEAR!!!!!

Click to expand...

To get into your original question - this would be a good one to ask the instructor of your class. I will ask around as it's a doozy for me. A few points:

1. Typical combined queries run INSERTS. Like Panic Mode's SELECT INTO query, which, I think, is typically used for backups. My idea was an UPDATE version of the combination INSERT/SELECT. I don't know if that really exists.

INSERT table2 (col1, col2, col3)
SELECT col1, col2, col3
FROM table1
WHERE bita = 1

There might be an UPDATE/SELECT combo, but I'd be hesitant of too many sub-SELECT queries slowing you down. I got this from another forum. There's got to be a better way.

UPDATE table2 SET
col1=(select col1 from table1 where indexnum=table2.indexnum),
col2=(select col2 from table1 where indexnum=table2.indexnum),
col3=(select col3 from table1 where indexnum=table2.indexnum),
where bita=1


2. You will almost certainly need to break your requirement down into 2 separate queries.

3. If it's too complex, you might need to write a prepared statement.

4. If it's too complex, you might need to take a step back, re-evaluate what you're trying to accomplish, a figure out a simpler approach.

In any event, we'll be able to assist you better if you post a higher level description of what you're trying to accomplish and sample SQL queries that you're working with.