iaing1000
asked on
INSERT and UPDATE with minimal information about the table's fields
Hi,
I'm trying to write a function in code to copy a set of rows from a given table to the same table and then alter a couple of the fields (RefNumber field and a TimeStamp field).
I'd like to do a simple INSERT (to copy the existing required rows) and then perform an UPDATE on these same rows to modify the RefNumber and TimeStamp fields accordingly. This sounds easy enough...
However, the parameters passed to my function are the TableName, the existing RefNumber (identifying those rows selected to be copied) and the new target RefNumber (to be changed in an update). This means that I won't know the rows of the table, and so can only do something like:
INSERT INTO TableName SELECT * FROM TableName WHERE RefNumber=<ExistingRefNumb
...and then do an update of these new rows somehow — but it's not clear how I'd identify these newly created rows either.
The only commonality between the tables that can be passed to my function are that they all have a 'RefNumber' column and all have their own differently named auto-incrementing ID column.
I'm guessing I may need to write and then call a stored procedure which might identify the columns of table 'TableName' and then do the job in one call such as:
INSERT INTO TableName VALUES(Col1,Col2,...,RefNu
...there may of course be another approach (could I somehow know the auto-ID of the newly created rows in my first suggestion and update these accordingly?) but I'm no SQL expert.
Any ideas?
Thanks in advance
Iain
I'm trying to write a function in code to copy a set of rows from a given table to the same table and then alter a couple of the fields (RefNumber field and a TimeStamp field).
I'd like to do a simple INSERT (to copy the existing required rows) and then perform an UPDATE on these same rows to modify the RefNumber and TimeStamp fields accordingly. This sounds easy enough...
However, the parameters passed to my function are the TableName, the existing RefNumber (identifying those rows selected to be copied) and the new target RefNumber (to be changed in an update). This means that I won't know the rows of the table, and so can only do something like:
INSERT INTO TableName SELECT * FROM TableName WHERE RefNumber=<ExistingRefNumb
...and then do an update of these new rows somehow — but it's not clear how I'd identify these newly created rows either.
The only commonality between the tables that can be passed to my function are that they all have a 'RefNumber' column and all have their own differently named auto-incrementing ID column.
I'm guessing I may need to write and then call a stored procedure which might identify the columns of table 'TableName' and then do the job in one call such as:
INSERT INTO TableName VALUES(Col1,Col2,...,RefNu
...there may of course be another approach (could I somehow know the auto-ID of the newly created rows in my first suggestion and update these accordingly?) but I'm no SQL expert.
Any ideas?
Thanks in advance
Iain
DECLARE @NewRefNumber ...
DECLARE @NewTimeStampValue ...
SET @NewRefNumber = ...
SET @NewTimeStampValue = ...
INSERT INTO TableName
SELECT col1, col2, col3,
@NewRefNumber, @NewTimeStampValue,
col6, col7, col8, col9 --, ...
FROM TableName
WHERE RefNumber = <ExistingRefNumber>
Copy the other columns as they are, just gen a new values for the ref# and timestamp, and substitute those in the INSERT.
DECLARE @NewTimeStampValue ...
SET @NewRefNumber = ...
SET @NewTimeStampValue = ...
INSERT INTO TableName
SELECT col1, col2, col3,
@NewRefNumber, @NewTimeStampValue,
col6, col7, col8, col9 --, ...
FROM TableName
WHERE RefNumber = <ExistingRefNumber>
Copy the other columns as they are, just gen a new values for the ref# and timestamp, and substitute those in the INSERT.
ASKER
Hi,
Thanks for your responses, much appreciated.
What I'm not sure about is how to use the sys.columns indicated in the first answer to actually get the columnnames (which of course I don't actually know at the time the tablename is passed).
I imagine I'm then going to be calling a stored proc from my code(which happily looks like the sort of answer coming back) with the NewRefNumber, NewTimeStamp and the TableName — which at the time it's passed is just a name. The only knowledge of it I have is that it will definitely have a field called RefNumber and another called TimeStamp and there'll be an auto-id field.
The other columns in the table TableName are a mystery and so I have to find a way to use the INSERT (that looks like the correct way) but somehow substituting the columnnames obtained from the sys.columns function.
Maybe?....if I've understood so far,
Thanks
Iain
Thanks for your responses, much appreciated.
What I'm not sure about is how to use the sys.columns indicated in the first answer to actually get the columnnames (which of course I don't actually know at the time the tablename is passed).
I imagine I'm then going to be calling a stored proc from my code(which happily looks like the sort of answer coming back) with the NewRefNumber, NewTimeStamp and the TableName — which at the time it's passed is just a name. The only knowledge of it I have is that it will definitely have a field called RefNumber and another called TimeStamp and there'll be an auto-id field.
The other columns in the table TableName are a mystery and so I have to find a way to use the INSERT (that looks like the correct way) but somehow substituting the columnnames obtained from the sys.columns function.
Maybe?....if I've understood so far,
Thanks
Iain
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers for this , will have a look later today
Iain
Iain
ASKER
Hi,
Looking good. I've adapted it slightly as you can see to do the particular things I need and I seem to understand now what's going on from doing all that.
The remaining thing is to stop the resulting SQL from doing an INSERT of the AutoID columns. Any table being passed to this will have its first column as an auto-incrementing primary key, although the name will differ from table to table. Is there a way to spot these and hence leave them out of the column_names value?
Thanks ...(latest code below)
CREATE PROCEDURE MergeCopy
@table_name sysname, --include schema. if not default schema
@ref_number varchar(50), --chg data type as needed
@newref varchar(50),
@user_id int
AS
/* --for debugging only, as stand-alone code not part of proc
DECLARE
@table_name sysname, --include schema. if not default schema
@ref_number varchar(50) --chg data type as needed
@newref varchar(50) --chg data type as needed
@user_id int --chg data type as needed
SET @table_name = 'dbo.data_transfer_corpnos
SET @ref_number = '12345'
SET @newref = 'qwerty'
SET @user_id= 101
*/
DECLARE @column_names varchar(max)
DECLARE @sql varchar(max)
SELECT @column_names =
(SELECT '[' + c.name + '],'
FROM sys.columns c
WHERE
c.object_id = OBJECT_ID(@table_name) AND
c.name NOT IN ('Ref_No_FK', 'CreateTimeStamp', 'CreateUserID')
ORDER BY c.column_id
FOR XML PATH(''))
PRINT @column_names --for debugging only
SET @sql = '
INSERT INTO [' + CAST(@table_name AS varchar(128)) + '] (' + @column_names + 'Ref_No_FK, CreateTimeStamp, CreateUserID)
SELECT ' + @column_names + '''' + @newref +''', GetDate(), ' + CAST(@user_id as varchar(50)) + '
FROM [' + CAST(@table_name AS varchar(128)) + ']
WHERE Ref_No_FK = ''' + @ref_number + ''''
PRINT @sql --for debugging only
-- EXEC(@sql) --uncomment when ready to insert new row(s)
GO
Looking good. I've adapted it slightly as you can see to do the particular things I need and I seem to understand now what's going on from doing all that.
The remaining thing is to stop the resulting SQL from doing an INSERT of the AutoID columns. Any table being passed to this will have its first column as an auto-incrementing primary key, although the name will differ from table to table. Is there a way to spot these and hence leave them out of the column_names value?
Thanks ...(latest code below)
CREATE PROCEDURE MergeCopy
@table_name sysname, --include schema. if not default schema
@ref_number varchar(50), --chg data type as needed
@newref varchar(50),
@user_id int
AS
/* --for debugging only, as stand-alone code not part of proc
DECLARE
@table_name sysname, --include schema. if not default schema
@ref_number varchar(50) --chg data type as needed
@newref varchar(50) --chg data type as needed
@user_id int --chg data type as needed
SET @table_name = 'dbo.data_transfer_corpnos
SET @ref_number = '12345'
SET @newref = 'qwerty'
SET @user_id= 101
*/
DECLARE @column_names varchar(max)
DECLARE @sql varchar(max)
SELECT @column_names =
(SELECT '[' + c.name + '],'
FROM sys.columns c
WHERE
c.object_id = OBJECT_ID(@table_name) AND
c.name NOT IN ('Ref_No_FK', 'CreateTimeStamp', 'CreateUserID')
ORDER BY c.column_id
FOR XML PATH(''))
PRINT @column_names --for debugging only
SET @sql = '
INSERT INTO [' + CAST(@table_name AS varchar(128)) + '] (' + @column_names + 'Ref_No_FK, CreateTimeStamp, CreateUserID)
SELECT ' + @column_names + '''' + @newref +''', GetDate(), ' + CAST(@user_id as varchar(50)) + '
FROM [' + CAST(@table_name AS varchar(128)) + ']
WHERE Ref_No_FK = ''' + @ref_number + ''''
PRINT @sql --for debugging only
-- EXEC(@sql) --uncomment when ready to insert new row(s)
GO
In this part extend the list with names that have an identity like 'AutoID '
SELECT @column_names =
(SELECT '[' + c.name + '],'
FROM sys.columns c
WHERE
c.object_id = OBJECT_ID(@table_name) AND
c.name NOT IN ('Ref_No_FK', 'CreateTimeStamp', 'CreateUserID') -- EXCLUDE LIST
ORDER BY c.column_id
FOR XML PATH(''))
SELECT @column_names =
(SELECT '[' + c.name + '],'
FROM sys.columns c
WHERE
c.object_id = OBJECT_ID(@table_name) AND
c.name NOT IN ('Ref_No_FK', 'CreateTimeStamp', 'CreateUserID') -- EXCLUDE LIST
ORDER BY c.column_id
FOR XML PATH(''))
Good point about the "auto" columns, I should of thought of that.
Yes, let's add a couple of checks to the SELECT from sys.columns:
WHERE
c.object_id = OBJECT_ID(@table_name) AND
c.name NOT IN ('Ref_No_FK', 'CreateTimeStamp', 'CreateUserID') AND
c.is_identity = 0 AND
c.is_rowguidcol = 0 AND
c.is_computed = 0
Yes, let's add a couple of checks to the SELECT from sys.columns:
WHERE
c.object_id = OBJECT_ID(@table_name) AND
c.name NOT IN ('Ref_No_FK', 'CreateTimeStamp', 'CreateUserID') AND
c.is_identity = 0 AND
c.is_rowguidcol = 0 AND
c.is_computed = 0
ASKER
EXcellent thanks loads,
Iain
Iain
You can find it in sys.columns, there is also a column is_identity that indicates if it's a identity column (that shouldn't be in that insert-list)
How to easy construct list see at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9dd4caf4-443b-41d5-aadc-44e6cc1bb602
I think you best include directly the right value of RefNumber and TimeStamp in this insert statement. No second action needed on same record then.
<< (could I somehow know the auto-ID of the newly created rows in my first suggestion and update these accordingly?) >>
@@IDENTITY or safer SCOPE_IDENTITY() gives value of last used identity
http://msdn.microsoft.com/en-us/library/ms187342.aspx
http://msdn.microsoft.com/en-us/library/ms190315.aspx
But identity is not for use in your case.