2/19/2025 3:58:13 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Duplicating a table
Posted: 16 Jul 07 7:42 AM
|
Hi,
There is a requirement of creating a dummy table in the dev environment and copy records from an existing table into the dummy table. Could anyone plz let me know how to do this ?
I tried the following SQL but it worked only once and now is throwing an error saying - FAILED TO PARSE SQL. 
'Select Fieldname into Dummytable from Orignltable'
TIA.
Richa. |
|
|
|
Re: Duplicating a table
Posted: 16 Jul 07 8:04 AM
|
You say "it worked only once" - that probably means it created the new table, running it again will fail because the table is already there. I guess you will have to do something like:
INSERT INTO DummyTable
SELECT * FROM Orignltable
But I have not tried it |
|
|
|
Re: Duplicating a table
Posted: 16 Jul 07 10:06 AM
|
The table already existing is definitely the issue. If this temp table is something you will always be using it might be best to define it through the Database manager and then you can always use INSERT...SELECT FROM to use it.
John G. |
|
|
|
Re: Duplicating a table
Posted: 16 Jul 07 11:55 PM
|
Hi,
I've already defined the temp table thru the DB manager. But the INSERT...SELECT..FROM is not working.
The dummy table has 2 fields which are there in the original table as well, its the value of these 2 filds that have to be copied. It's now throwing this error : INSERT ERROR - Column name or number of supplied values does not match table definition.
Plz guide. TIA.
Richa. |
|
|
|
Re: Duplicating a table
Posted: 17 Jul 07 12:59 AM
|
Please post your INSERT INTO query. It should be of the form:
INSERT INTO DummyTable(field1, field2)
Select field1, field2 from OrigTable
If you're on V7+, make sure that you do not write to the CREATEDATE, CREATEUSER, MODIFYDATE and MODIFYUSER fields explicitly - the provider takes care of these. Or are you running the update in native SQL?
What are you doing about the IDs - keeping the same ones or generating new ones? |
|
|
|
Re: Duplicating a table
Posted: 17 Jul 07 7:39 AM
|
Yes, post your SQL here. You're are definitely running into the issue of values being INSERTed not matching the destination table fields.
In its simplest form, an INSERT can be written as
INSERT INTO destination
VALUES (value1, value2, value3)
For this to work, VALUES must be defined for each field in the table. The above example will work only if there are three fields in the [destination] table. The only exception to this is an identity field. You cannot specify a value for an identity field as it is automatically populated by the database. Identity fields are left out of the field list.
Specifying the field each VALUE is to be INSERTed is the best practice method of forming an INSERT statement. For example,
INSERT INTO destination
(field1, field2)
VALUES
('Testing', 123)
The above will work if there are only 2 fields in the database and one or more identity fields. The reason is because the fields to be INSERTed are listed. The above may not produce an error if there were three fields in the [destination] table. You'll notice I said may not produce an error. I say this because the 3rd field may have a constraint such as NOT NULL. In this case you'll get the error "Cannot insert the value NULL into column..." A way around this would be to specify a default value for the field at the database level.
Specifying all the destination fields and VALUES is the safest way to use an INSERT statement. Using * and assuming all the fields are there in the order you want them is always suspect to errors.
John G. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|