Hi
If you specify the column list then you insert values into those columns.
Missing columns will be set to NULL (if it is allowed) or a default value.
To update existing data use the UPDATE statement, you will only change the
values for columns specified in the SET clause. More information on the
INSERT and UPDATE statements can be found in Books Online.
Using stored procedure may help to remove any issues with different versions
as you will ship the correct procedure with the changes to the schema.
You can look at the INFORMATION_SCHEMA.columns view to find out what columns
a table has, although a different approach would be to only provide a
solution for the latest version and check they are on that version otherwise
force an upgrade.
John
"SA Development" wrote:
> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either ha
ve
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>Thanks for the good ideas everyone, sorry my first post wasn't entirely
clear.
Lets say I have a table like this:
field1(key) field2 field3 field4 field5
9999 a b c d
What I was hoping to do is add a new record (field1=123 for example), but
use the 9999 as a template record so that its field values are inserted into
my new record EXCEPT for any that I override.
It sounds like the update command will let me update just the fields I want,
is there a way to tell SQL to copy record 9999 to record 123 and use all the
field values in record 9999 ?
Thanks,
Alan|||Hi David,
> INSERT INTO YourTable (col1, col2, col3, col4, col5)
> SELECT 123, col2, col3, col4, col5
> FROM YourTable
> WHERE col1 = 9999
Thank you -- that is exactly what I needed!
Thanks to everyone else who posted as well.
Have a great day,
Alan
Monday, March 19, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment