I am attempting to make some cross-DB(SQL Server and PostgreSQL) compatible SQL. What I am needing to do is clone a row. It is preferable to do it locally on the SQL server without having to pull the entire row down to our client and reinsert it. Also, we prefer to not have to create a new SQL query from scratch putting in column names dynamically, but this is an option I suppose.
For example: We have an address table in it is a table like so: address_rid: integer, primary key, auto-incrementing, not null address1: varchar(100) address2: varchar(100)
Well, our goal is to just be able to clone one of these rows so that address1 and address2 are the same, yet the address_rid would be set to the next value as usual.
How would you go about doing this?
Also, I have seen Quickest way to clone row in SQL
which has
INSERT INTO PrimKeys
SELECT 'PrimKey2' AS PrimKey,*
FROM PrimKeys
WHERE PrimKey='PrimKey1'
I'm having trouble getting something like this to work on postgres and also to use something like default on the primary key that gets inserted.
edit: also, I'd just as well take 2 separate queries that will do this on SQL Server and Postgres, though I'd prefer to just have to maintain 1 query.
and I am using the C# ado.net as my client.
I'm not sure about PostgreSQL but if it supports the auto-increment like most systems do then just leave the primary key field blank. However this does mean you need to specify the column names.
Also, when you say clone it sounds like you want a duplicate of your record but in your description the address1 and address2 columns are on the same row of data.
If you want to replicate your address1 to the address2 column you can use a simple update statement.
If you are trying to clone the entire row to have two records that are exactly the same then you could use something like this and it should work on both environments.
INSERT INTO Addresses
( address1, address2 )
SELECT address1, address2
FROM Addresses
WHERE addressId = ID
Now the next question is, Are you cloning from one DB type to the other? If so then you will need to pull it into your app anyway. If you are then why don't you use something like nHibernate to abstract the db layer away from you.
If the goal is to just make a copy then you can do it straight in the db with the sql above.
While this sql is probably the easiest (and you could reflect it from the db if needed using system / master tables) you would still need to specify the column names. The primary reason is that as soon as you add the * wildcard it would still try to include your original primary key column in the select list which would then have 1 extra column to what you have.
Another issue to be aware of when using wildcards in insert statements is that your column orders HAVE TO MATCH otherwise you will get very unexpected results.