1

In my SQL Server table, I have a table whose PK is GUID with lots of records already.

Now I want to add records which only needs to change the COMMON_ID and COMMON_ASSET_TYPE column of some existing records.

select * from My_Table where COMMON_ASSET_TYPE = "ASSET"

I am writing sql to copy above query result, changing COMMON_ID value to new GUID value and COMMON_ASSET_TYPE value from "ASSET" to "USER", then insert the new result into My_Table.

I do not know how to write it since now I feel it is a trouble to insert records manually.

enter image description here

Update:

I have far more columns in table and most of them are not nullable, I want to keep all these columns' data for new records except above two columns.Is there any way if I do not have to write all these column names in sql?

Ryan
  • 19,118
  • 10
  • 37
  • 53

3 Answers3

1

Try to use NEWID if you want to create new guid:

INSERT INTO dbo.YourTable
(
    COMMON_ID,
    COMMON_ASSET_TYPE
)
select NEWID(), 'User' as Common_Asset_Type 
from My_Table 
where COMMON_ASSET_TYPE = "ASSET"

UPDATE:

As a good practice I would suggest to write all column names explicitly to have a clean and clear insert statement. However, you can use the following construction, but it is not advisable in my opinion:

insert into table_One
select 
  id
, isnull(name,'Jon') 
from table_Two
StepUp
  • 36,391
  • 15
  • 88
  • 148
  • Thanks it should work when other columns are nullable.But I have lots of column(20+),and some of them can not be null, so I want to use all original record except the two column. is there any way I do not have to specify each cloumn name in sql? – Ryan Aug 27 '20 at 09:23
1
INSERT INTO My_Table (COMMON_ID,COMMON_LIMIT_IDENTITY, COMMON_CLASS_ID,COMMON_ASSET_TYPE)
SELECT NEWID(), COMMON_LIMIT_IDENTITY, COMMON_CLASS_ID,'USER'
  FROM My_Table 
 WHERE COMMON_ASSET_TYPE = 'ASSET'
Conffusion
  • 4,335
  • 2
  • 16
  • 28
  • Thanks,it works when other columns are nullable.But I have lots of column(20+),and some of them can not be null, so I want to use all original record except the two column. is there any way I do not have to specify each cloumn name in sql? – Ryan Aug 27 '20 at 09:21
  • You're talking about a one-time effort to copy-paste 20+ column names. The time you spend to search for an alternative (without any guarantee of success), the job is done. See https://stackoverflow.com/questions/29095281/how-to-select-all-the-columns-of-a-table-except-one-column/29232177 for another discussion on this topic. – Conffusion Aug 27 '20 at 09:53
0

If I've understood correctly you want to take existing records in your table, modify them, and insert them as new records in the same table.

I'll assume ID column contains the the GUID?

I'd first create a temporary table

CREATE TABLE #myTempTable( ID UNIQUEIDENTIFIER, Name varchar(max), ... etc );

Fill this temp table with the records to change with your SELECT statement.

Change the records in the temp table using UPDATE statement.

Finally, Insert those "new" records back into the primary table. with INSERT INTO SELECT statement.

You will probably have to sandwitch the INSERT INTO SELECT with IDENTITY_INSERT (on/off):

SET IDENTITY_INSERT schema_name.table_name ON

SET IDENTITY_INSERT schema_name.table_name OFF

IDENTITY_INSERT "Allows explicit values to be inserted into the identity column of a table."

Agneum
  • 727
  • 7
  • 23