Tuesday, March 28, 2017

How to copy a table in SQL Server


Sometimes there may be requirement of a user to copy a table in a database which exists in the SQL database management studio. The new table might be created for temporary backup while performing data changes in the original table.

The new table may contain all the data records from the original table or some of the rows based on filter applied to select the data. Also, it is not necessary to copy all the columns from the original table. The user can select the columns based on the requirement.


Syntax:

      SELECT [ALL | DISTINCT] select_column_list
      [INTO [new_table_name]]
      FROM {TABLE_NAME | VIEW_NAME}
      [CLAUSES];

[] = Optional code


ALL is represented with an (*) asterisk symbol and displays all the columns of the table.

DISTINCT specifies that only the unique rows should appear in the result set.

select_column_list is the list of columns for which the data is to be listed.

INTO creates a new table and inserts the resulting rows from the result-set into it.

new_table_name is the name of the new table to be created.

FROM specifies the location from where the data is retrieved. Either from a table or from a view.

CLAUSE(S) specifies the search conditions for the rows returned by the query. It may include following clauses:

·         WHERE clause
·         GROUP BY clause
·         HAVING clause
·         ORDER BY clause

Note: If no condition is specified, all the data records will be copied into the new table.


Example 1: To copy complete table.

        SELECT *
INTO dbo.TempPerson
FROM Person.Person



Example 2: To copy selected data records into new table.

          SELECT *
INTO dbo.TempPersonMale
FROM Person.Person
WHERE Title = 'Mr.'



Example 3: To copy selected data columns into new table.

SELECT
PersonType, Title, FirstName, MiddleName, LastName
INTO dbo.TempPersonFemale
FROM Person.Person
WHERE Title = 'Ms.'



Example 4: To copy table into new table when new table already exist.

SELECT
PersonType, Title, FirstName, MiddleName, LastName
INTO dbo.TempPersonFemale
FROM Person.Person
WHERE Title = 'Ms.'

The above query will return an error since the destination table is created, it should not exist in database.




Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.

0 comments: