Solved

INSERT and UPDATE with minimal information about the table's fields

Posted on 2012-03-14
9
282 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:iaing1000
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37719684
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37721017
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
 

Author Comment

by:iaing1000
ID: 37722206
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 37722354
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:iaing1000
ID: 37723749
Cheers for this , will have a look later today

Iain
0
 

Author Comment

by:iaing1000
ID: 37725891
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
 
LVL 25

Expert Comment

by:jogos
ID: 37726254
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37726265
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
 

Author Closing Comment

by:iaing1000
ID: 37728630
EXcellent thanks loads,

Iain
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now