How to Copy a Row with Every Column Except Identity Column (Sql Server 2005)

How to copy a row with every column except identity column (SQL Server 2005)

DECLARE 
    @cid INT,
    @o   INT,
    @t   NVARCHAR(255),
    @c   NVARCHAR(MAX),
    @sql NVARCHAR(MAX);

SELECT
    @cid = 10,
    @t   = N'dbo.CTABLE',
    @o   = OBJECT_ID(@t);

SELECT @c = STRING_AGG(QUOTENAME(name), ',')
    FROM sys.columns
    WHERE [object_id] = @o
    AND is_identity = 0;

SET @sql = 'SELECT ' + @c + ' INTO #t 
    FROM ' + @t + ' WHERE CID = @cid;

    INSERT ' + @t + '('+ @c + ') 
    SELECT ' + @c + ' FROM #t;' 

PRINT @sql;
-- exec sp_executeSQL @sql,
--  N'@cid int',
--  @cid = @cid;

However it seems much easier to just build the following SQL and avoid the #temp table altogether:

SET @sql = 'INSERT ' + @t + '(' + @c + ') 
    SELECT ' + @c + ' FROM ' + @t + '
    WHERE CID = @cid;';

PRINT @sql;
-- exec sp_executeSQL @sql,
--  N'@cid int',
--  @cid = @cid;

How do you copy a record in a SQL table but swap out the unique id of the new row?

Try this:

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

SQL Server : multiple rows with same column values except one

This may be helpful when a record may have the same unique id but have different values on every column. Make use of ROW_NUMBER and window function. I guess they are already present on sql-server 2005.

SELECT name_text,c_num,d_type,unique_id,v_text,r_ticket_id
FROM
    (
        SELECT  name_text, c_num, d_type, unique_id, v_text, r_ticket_id,
                ROW_NUMBER() OVER (PARTITION BY unique_id 
                                   ORDER BY r_ticket_id DESC) rn
        FROM tableName
    ) x
WHERE x.rn = 1

SQLFiddle Demo

How to drop IDENTITY property of column in SQL Server 2005

IF you are just processing rows as you describe, wouldn't it be better to just select the top N primary key values into a temp table like:

CREATE TABLE #KeysToProcess
(
     TempID    int  not null primary key identity(1,1)
    ,YourKey1  int  not null
    ,YourKey2  int  not null
)

INSERT INTO #KeysToProcess (YourKey1,YourKey2)
SELECT TOP n YourKey1,YourKey2  FROM MyTable

The keys should not change very often (I hope) but other columns can with no harm to doing it this way.

get the @@ROWCOUNT of the insert and you can do a easy loop on TempID where it will be from 1 to @@ROWCOUNT

and/or

just join #KeysToProcess to your MyKeys table and be on your way, with no need to duplicate all the data.

This runs fine on my SQL Server 2005, where MyTable.MyKey is an identity column.

-- Create empty temp table
SELECT *
INTO #TmpMikeMike
FROM (SELECT
      m1.*
      FROM MyTable                 m1
          LEFT OUTER JOIN MyTable  m2 ON m1.MyKey=m2.MyKey
      WHERE 1=0
 ) dt

INSERT INTO #TmpMike
SELECT TOP 1 * FROM MyTable

SELECT * from #TmpMike

EDIT
THIS WORKS, with no errors...

-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM (SELECT
          m1.*
          FROM MyTable                 m1
              LEFT OUTER JOIN MyTable  m2 ON m1.KeyValue=m2.KeyValue
          WHERE 1=0
     ) dt
...
WHILE ...
BEGIN
    ...
    INSERT INTO #Tmp_MyTable
    SELECT TOP (@n) *
    FROM MyTable
    ...

END

however, what is your real problem? Why do you need to loop while inserting "*" into this temp table? You may be able to shift strategy and come up with a much better algorithm overall.

How to update Identity Column in SQL Server?

You can not update identity column.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

• When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it's needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

Example:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

SQL query for selecting all items except given name and all rows with id of that given name

Simplest is probably to do the join to get the results as usual, and then just eliminate all car_model_ids that exist in Germany;

SELECT cm.car_model_id, ci.car_location_name, cm.car_name
FROM CAR_MODEL cm
JOIN CAR_INVENTORY ci
  ON cm.car_model_id=ci.car_model_id
WHERE cm.car_model_id NOT IN (
  SELECT car_model_id FROM CAR_INVENTORY WHERE car_location_name='Germany'
)

An SQLfiddle to test with.

Get top first record from duplicate records having no unique identity

The answer depends on specifically what you mean by the "top 1000 distinct" records.

If you mean that you want to return at most 1000 distinct records, regardless of how many duplicates are in the table, then write this:

SELECT DISTINCT TOP 1000 id, uname, tel
FROM Users
ORDER BY <sort_columns>

If you only want to search the first 1000 rows in the table, and potentially return much fewer than 1000 distinct rows, then you would write it with a subquery or CTE, like this:

SELECT DISTINCT *
FROM
(
    SELECT TOP 1000 id, uname, tel
    FROM Users
    ORDER BY <sort_columns>
) u

The ORDER BY is of course optional if you don't care about which records you return.

---

Related Topics