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=<ExistingRefNumber>

...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,...,RefNumber, TimeStamp,....ColN) SELECT Col1, Col2,...,<NewRefNumber>, <TimeStampVal>,...ColN  FROM TableName WHERE RefNumber=<ExistingRefNumber>

...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
iaing1000Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Oh, OK, please try this then:


CREATE PROCEDURE proc_name
    @table_name sysname, --include schema. if not default schema
    @ref_number varchar(30) --chg data type as needed
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(30) --chg data type as needed
SET @table_name = 'dbo.data_transfer_corpnos'
SET @ref_number = '12345'
*/

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 ('NewRefNumber', 'NewTimeStamp')
    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 + 'NewRefNumber, NewTimeStamp)
SELECT ' + @column_names + '''NewRefNumberValue'', ''NewTimeStampValue''
FROM [' + CAST(@table_name AS varchar(128)) + ']
WHERE RefNumber = ''' + @ref_number + ''''

PRINT @sql --for debugging only
--EXEC(@sql) --uncomment when ready to insert new row(s)

GO
0
 
jogosCommented:
You need to specify the columnlist (only the columns from both tables).

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.
0
 
Scott PletcherSenior DBACommented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
iaing1000Author Commented:
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
0
 
iaing1000Author Commented:
Cheers for this , will have a look later today

Iain
0
 
iaing1000Author Commented:
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
0
 
jogosCommented:
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(''))
0
 
Scott PletcherSenior DBACommented:
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
0
 
iaing1000Author Commented:
EXcellent thanks loads,

Iain
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.