Result of one Table into Another table in SQL

Result of one Table into Another table in SQL

Some times you have  requirement like you want to copy data of one table to another table or you want to have a new table like old table There are many ways to achieve this I will explain you all in each points with query and Scripts

Below is the script of table to be copied

--New table

CREATE TABLE [dbo].[testcopy](

 [column1] [nvarchar](50) NULL,

 [column2] [nvarchar](50) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'5', N's')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'4', N'd')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'8', N'f')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'9', N'r')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'6', N't')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'8', N'y')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'7', N'h')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'3', N'u')

INSERT [dbo].[testcopy] ([column1], [column2]) VALUES (N'6', N'i')

1-Creating a new table along with data using above table Script i.e. copying structure and data using query

Select * into NewTestCopy from Testcopy

--to create a temp table using above table

Select  * into #NewTestcopy from TestCopy

--try to create a temptable using Select into

Select * into @NewTestcopy from TestCopy

This will throw an error as Select * into doesn't work with table variables

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '@NewTestcopy '.

2-Copying data only from above table to another table or temp table or table variable

For this we will create a new table

CREATE TABLE [dbo].[testcopy1](

 [column1] [nvarchar](50) NULL,

 [column2] [nvarchar](50) NULL

) ON [PRIMARY]

GO

--Copying Data of testcopy to testcopy2

insert into testcopy1

select * from testcopy 

3-Copying data only of selected column from above table  to another table selected columns

insert into testcopy1(column1)

select column1 from testcopy 

4-If no. of Rows are less you can use GUI also to copy data from one table to another.

Right click -Sourcetable-Edit top 200 rows-Select All-Copy all data

Right click -Destination Table-Edit top 200 rows-Paste.

5-Using Import and Export wizard.

 selecting your source and Destination table or you can create Destination table Same as Source table along with data but same drawback that it will not create contraints and key in the destination table.

Steps
-Rightclick ur Database-Tasks-Export data or Import Data-Choose source Datasource-Database-

Choose Destination-database table-Next-Choose Data from table and view-Select source table and view

-Next-finish.You can select multiple table ,single table or all tables to be copied from one database to another.

Note :

1- Select into TableName will create a new table with same table structure and data but will not copy keys & constraints.

2-Insert into with Select * will work with both Tempory table and Table variable but select * into will not work with table variable.