Insert Data From One Table To another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert Data From One Table To another Table

Author

Topic

Atul Gupta
Starting Member

2 Posts

Posted - 2010-08-02 : 19:08:56

Hi,

I am new to SQL and have a query how to insert the rows from one table to another.

I am having one table by the name say XYZ having composite primary key(machineid + stationid).

I have created an exact duplicate table say ABC. My requirement is to insert the new rows coming in the table XYZ into Table ABC on regular basis without deleting the existing contents of ABC.

I tried this :

INSERT INTO [invdb].[dbo].[ABC] ( [machine_id],[serial_no],[station_id],[process_id])
SELECT [invdb].[dbo].[XYZ].[machine_id]
,[invdb].[dbo].[XYZ].[serial_no]
,[invdb].[dbo].[XYZ].[station_id]
,[invdb].[dbo].[XYZ].[process_id]
FROM [invdb].[dbo].[XYZ]
WHERE ([invdb].[dbo].[XYZ].[machine_id]<>[machine_id] AND [invdb].[dbo].[XYZ].[station_id]<>[station_id])

But it is not working. Please help.

Regards,
Atul Gupta

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-02 : 19:21:03

depending on your data, you can create this using WHERE NOT EXISTS statement.

can you provide some sample data. Are you having any date or time column or any varbinary fields or anything that is normally used for replication purposes?

Atul Gupta
Starting Member

2 Posts

Posted - 2010-08-02 : 19:36:27

Hi,

Thanks for the suggestion. I have only the below mentioned columns in the table:

[machine_id],[serial_no],[station_id],[process_id]

and no date time field.

The whole issue lies here:

WHERE ([invdb].[dbo].[XYZ].[machine_id]<>[machine_id] AND [invdb].[dbo].[XYZ].[station_id]<>[station_id])

My Where Clause is not working and i don't know why.
I tried this also but it is giving me some bounding error:

WHERE ([invdb].[dbo].[XYZ].[machine_id]<>[invdb].[dbo].[ABC].[machine_id] AND [invdb].[dbo].[XYZ].[station_id]<>[invdb].[dbo].[ABC].[station_id])

I want to insert only those rows from Table XYZ whose machine Id + station ID i.e Primary key is not present in table ABC.

I also tried with WHERE EXISTS clause but not able to use it properly.

regards,
AtulGupta

quote:
Originally posted by slimt_slimt

depending on your data, you can create this using WHERE NOT EXISTS statement.

can you provide some sample data. Are you having any date or time column or any varbinary fields or anything that is normally used for replication purposes?

konark
Yak Posting Veteran

60 Posts

Posted - 2010-08-02 : 19:43:24

Usually ETL processes apply these logic.

Update existing and insert new ( UPSERT)

--Update exiting
update dbo.ABC
SET serial_no=a.serial_no,
process_id=a.process_id
FROM dbo.XYZ a JOIN dbo.ABC b on a.machine_id=b.machine_id and a.station_id=b.station_id

- Insert new
Insert into dbo.ABC
select a.machine_id, a.serial_no, a.station_id,a.process_id
from dbo.XYZ a left join dbo.ABC b
on a.machine_id=b.machine_id and a.station_id=b.station_id
where b.machine_id is null or b.station_id is null

Chandragupta Mourya

jwmayo
Starting Member

28 Posts

Posted - 2010-08-16 : 14:26:46

Thank you looks like I got it now... Took me a while new to this Thanks for all the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources