I get an error when I try:
CREATE TABLE new_table AS
SELECT * FROM old_table
Error in syntax by select.
MS SQL
I am not sure, but maybe it did partially work?
I did make a new table and get the data copied over.
I tried the code above and got an error.
I then tried this code and got an error:
SELECT *
INTO new_table
FROM old_table
WHERE 1 = 0;
I thought I got errors every try, but the table was created with the same headers.
I thought the error said something like this query did not return a set of data.
Then this worked:
INSERT INTO new_table
SELECT *
FROM old_table;
8.1.19
The CREATE TABLE statement needs parentheses around the select statement to conform to the SQL standard, but doesn't appear to be supported by MS SQL Server.
For your second try, you selected zero rows with your WHERE clause so creating a table with zero rows in it (and a message saying your query didn't return any data) sounds like success to me.
2 Likes
I don't like when it seems like a bug when it works.
I got errors each time.
It did work though.
If you're making a new table from scratch with the SELECT statement in SQL Server I believe you want SELECT * INTO NewTable FROM OldTable; However, once that table exists, then you need to do INSERT INTO NewTable (col1, ..., colN) SELECT col1, ..., colN FROM OldTable Select into only works if that table does not exist yet iirc.
I have never seen it done this way before CREATE TABLE new_table AS SELECT * FROM old_table.
1 Like
Select * into newTable from oldTable where 1=0
Interesting, this works even in read only.
I got an error it was an empty set, but it did create the table.
I attempted to insert by order, but that did not work.
INSERT INTO new_table
SELECT *
FROM old_table order by Line, Sku
Unfortunately though, the inserts just ignored the order.
Was the error from ignition? Because SELECT * INTO newTable FROM oldTable does not return a result set. Not in SSMS so Ignition most definitely is not getting one. I suspect that is why you see that "empty set" error. Ignition thinks you're running a SELECT, is expecting a result set, but SSMS is not providing one becuase SELECT * INTO newTable FROM oldTable does not provide one.
If you copy one of your tables to a dummy table, I think you can see it easy.
I am on 8.1.19 though.
Why does your SELECT statement have WHERE 1 = 0?
I would imagine he's trying to create the same table structure without any data in it.
However, what is the real issue here? That you are getting the empty set error when you try to create with a SELECT * INTO newTable FROM oldTable when your in read only mode in Ignition? I would say this is completely reasonable and expected behavior and not a bug at all.
Either create your tables directly in SSMS, or put Ignition read/write mode and use create table statements, though I would 100% opt for SSMS out of these two.
SELECT INTO is probably very confusing for Ignition's parser, I would guess it sees the SELECT keyword and automatically excepts it to be a SELECT type query with a result set, not to be a UPDATE/CREATE table type of statement. I recall @PGriffith telling me a while ago the SQL parser is pretty basic. Maybe he can chime in when he logs on.
Regardless I would not be designing the schema through Ignition's query browser if you could help it.
2 Likes
The errors were just confusing me.
The data is getting into the tables.
Solution:
Select * into newTable from oldTable where 1=0
Well, it is the solution for now.
I mean it would be nice to make the 'order by' work when inserting.
@Tim_Carlton
If I want the data to copy over, I don't use the where 1=0.
If I only want the headers and datatypes, I use where 1=0.
I prefer to develop my schema from Ignition for Ignition.
If it is something I can't view or change from Ignition, I don't want it in my DB for Ignition.
My goal is that a person would only need to access Ignition to see all the things that were used in the development of anything used in the project.
I like the one source of truth principle, and accept the tradeoffs.
There are things that would be much easier to do with DB functions that aren't visible in Ignition for sure.
It is a preference.
1 Like
SELECT INTO should make the tables identical in structure and the way the rows are ordered without things like constraints / triggers.
FWIW I don't think you can insert with an ORDER BY though maybe SSMS allows it, it allows a lot of stuff others don't, because in general with RDM databases the order of inserts should not matter. No matter what your insert order is, doing SELECT * FROM old_table order by Line, Sku will return the same result set if everything was inserted in the same order or in reverse order, the insert order does not matter.
2 Likes
Still, when I see my nice organized table with one row out at the bottom out of order, my CDO flares.
Then sort it by whatever you want when you want to look at it, OCD avoided !
3 Likes