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-
-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.
No comments:
Post a Comment