loading data from one table to another

Question

Post reply

loading data from one table to another

athi_ssn

Old Hand

Points: 328
More actions
May 28, 2008 at 9:04 am

#182794

Hi All,
We have two tables one is stg_product(staging table) and other is product. Both have same table structure with columns like account_number, branch_number, product_number, product_name, etc., We have some rows in stg_product table which is not present in product table. So we need to first find out only those rows which doesn’t exist in product table, extract those rows and load it in product table. Could you please tell me how to do this? The database is SQL Server.
Can we do it using INSERT INTO.. SELECT exists subquery? or is there any other way to do?
Thanks in advance
Priya.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Please post the DDL (CREATE TABLE) scripts for your two tables, including primary key definitions.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

athi_ssn Old Hand Points: 328 More actions · Answer 2

Hi, its a big table so the sript is very big indeed. this is the ddl fro staging table.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stg_na10ndel66]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[stg_na10ndel66]

GO

CREATE TABLE [dbo].[stg_na10ndel66] (

[run_identity] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[biz_date] [datetime] NULL ,

[client_id] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[branch_id] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[account_id] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rec_id_1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rec_id_2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[segment_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[pc_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[firstname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[middlename] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lastname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[title] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[j_firstname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[j_middlename] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[j_lastname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[j_title] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[short_name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[street_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[appt_no] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[city] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[zip_code_1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[zip_code_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[zip_code_3] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[cad_post_1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[cad_post_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fg_post_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fg_country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

and for the another table it is as follows:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cur_naa_master]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[cur_naa_master]

GO

CREATE TABLE [dbo].[cur_naa_master] (

[run_identity] [decimal](18, 0) NOT NULL ,

[eff_date] [datetime] NULL ,

[exp_date] [datetime] NULL ,