Copy or Duplicate a Database Record in SQL

There are a few ways to copy all the column values from a record (except for the auto-generated identity) into a new row. If the table only has a few columns (for example, a BookStore table has storeId as its identity, storeName, bookTitle and price columns, and you want to duplicate the row with StoreId 2), you may simply run

insert into BookStore(nstoreName, bookTitle,price)
select storeName, bookTitle, price from BookStore where storeId=2

Typing the column names may become tedious if the BookStore table has many columns. In such case, there is another quick way

select *  into TempBookStore from BookStore where storeId=2
alter table tempBookStore drop column storeId
insert into BookStore select * from tempBookStore
drop table tempBookStore
Advertisements
This entry was posted in Database and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s