Import the contents of one table into another in SQL Server

If I have two tables with identical columns and types, how would I import the contents of one table into the other? Is there a way to do this and empty the exporting table at the same time? Finally is there a way to exclude and delete any duplicate fields?
bpfsrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
how would I import the contents of one table into the other?
Use Import/export tasks. from your database >> tasks >> Import data or export data. follow the wizard,

Is there a way to do this and empty the exporting table at the same time?
Better use TSQL script to empty the exporting table. Use DELETE or TRUNCATE

Finally is there a way to exclude and delete any duplicate fields?
Fields?  I don't get this clear.
0
bpfsrAuthor Commented:
what i am saying is if the exporting table has a field already in the importing table I would want the field to be rejected rather than accepted and therefore create a duplicate field...
0
bpfsrAuthor Commented:
I don't see Import/Export under tasks, I have SQL Server Express, is this an Enterprise function?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Faiga DiegelSr Database EngineerCommented:
Oh.. express edition don't have that feature.
0
szabogiCommented:
Are you trying to set this up as some sort of automated process OR just an ad-hoc, one time thing?  If the latter, it will be a lot less effort to use TSQL to push your records into the other table.
0
szabogiCommented:
Here is some simple TQL source code to demonstrate how to create duplicate table schemas based on existing tables, copy data from one table to another table with same schema, and how to duplicate a table with the data.  This may provide you with some ideas for how to handle your situation.
-- Duplicate Table Schema: creates a table matching column and datatype definition but inserts NO rows
select top 0 *
into composite2
from composite
 
-- copy all the data from the composite table to the newly created composite2 table
insert composite2
select * from composite
 
-- Duplicate Table Schema and Data: selects all rows from composite table into new composite2 table.  
select *
into composite2
from composite

Open in new window

0
bpfsrAuthor Commented:
szabogi:
That is very close to what I want but I am looking to import new data into an existing table which already has data stored. So Table 1 will have data in it, Table 2 will have new data in it. Both tables will have identical columns and data types. I want to add the data from Table 2 to Table 1 while screening for any possible duplicates (i.e. if Table 2 has a record already in Table 1). Thank you.
0
szabogiCommented:
bpfsr:

Here are two examples that should accomplish what you want.  The first example uses a concatenation technique to prevent dupes that is useful when you do not have an identity column or other keyed column to work with.

The second example shows how to handle things with an identity column.  When you have an identity column, you need to type out the column names AND need to use the IDENTITY_INSERT command to allow the identity values to be seeded into the destination table.
/*
Example 1.
 
tblOriginal is the original table.
tblDestination is the table we want to insert non-duplicate records into.
Both tables have identical schemas
 
For purpose of the example, there are 4 colums:
firstname, lastname, ssn, gender
 
*/
 
insert tblDestination
select firstname, lastname, ssn, gender
from tblOriginal
where firstname+lastname+ssn NOT IN (select firstname+lastname+ssn from tblDestination)
 
/* Example 2 -- handling things with an identity column
 
This example shows how you can manipulate the identity setting on 
the destination table to allow for inserts from the original table.
It also won't allow dupes to get inserted.
*/
 
 
create table tblOriginal (
 ID int IDENTITY(1,1) NOT NULL,
 firstname varchar(20),
 lastname varchar(25),
 ssn char(11),
 CONSTRAINT [PK_tblOriginal] PRIMARY KEY CLUSTERED
  (
	[ID] ASC
  )
)
 
create table tblDestination (
 ID int IDENTITY(1,1) NOT NULL,
 firstname varchar(20),
 lastname varchar(25),
 ssn char(11),
 CONSTRAINT [PK_tblDestination] PRIMARY KEY CLUSTERED
  (
	[ID] ASC
  )
)
 
-- insert 3 rows into original table
 
INSERT INTO tblOriginal (firstname, lastname, ssn)
SELECT 'Mickey', 'Mouse', '111-11-1111'
 
INSERT INTO tblOriginal (firstname, lastname, ssn)
SELECT 'Donald', 'Duck', '222-22-2222'
 
INSERT INTO tblOriginal (firstname, lastname, ssn)
SELECT 'Pluto', 'Dog', '333-33-3333'
 
 
-- copy these rows into tblDestination
 
SET IDENTITY_INSERT tblDestination ON
 
insert tblDestination (ID, firstname, lastname, ssn)
select ID, firstname, lastname, ssn from tblOriginal
where ID NOT IN (select ID from tblDestination)
 
SET IDENTITY_INSERT tblDestination OFF
 
-- run it again to verify that these 3 rows did NOT get copied again
 
SET IDENTITY_INSERT tblDestination ON
 
insert tblDestination (ID, firstname, lastname, ssn)
select ID, firstname, lastname, ssn from tblOriginal
where ID NOT IN (select ID from tblDestination)
 
SET IDENTITY_INSERT tblDestination OFF

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bpfsrAuthor Commented:
One last question then, and thank you for your patience, can I do the same thing using a view instead of a table, i.e. import into the existing table from a view?
0
szabogiCommented:
bpfsr:

Absolutely, your source table could be replaced by a view instead:


-- if the destination table doesn't already exist, use this:
select *
into tblDestination
from vwSource
 
-- if the destination table already exists use this:
 
insert tblDestination
select * 
from vwSource

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.