Copying Rows between Databases

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

Copying Rows between Databases

Author

Topic

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 12:07:56

I accidentally removed a lot of records in table1 of database1. I have a backup of the database that I have restored as database2.

Now, how do I copy rows (not all rows but all columns) from table1 of database2 to table1 of database1? The tables of course have the same columns in both databases.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 12:11:26

insert database1.dbo.table1
select * from database1.dbo.table1
where ...

No, you're never too old to Yak'n'Roll if you're too young to die.

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 12:15:15

No "INTO" after INSERT? I tried INSERT INTO but then SQL Server told me there was already a table1 in database1 (which is of course correct).

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 12:23:28

INTO is optional

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 12:26:12

quote:
Originally posted by nasu

No "INTO" after INSERT? I tried INSERT INTO but then SQL Server told me there was already a table1 in database1 (which is of course correct).

If SQL Server told you that then your Statement was like this:
select * INTO Table1 from ...
In this case SQL Server will create target table.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 12:44:09

OK, thanks, I have copied rows successfully. And, yes, that is the way my INSERT INTO was constructed when SQL Server objected.

Now a follow-up question. If I only want to insert rows if they do not already exist, e.g. the column providing a unique ID (column1) is different, how do I change my query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 12:55:38

do it like this:
insert database1.dbo.table1
select * from database2.dbo.table1
where ...
and not exists (select 1 from database1.dbo.table1 t1 where t1.uniqueColumn = database2.dbo.table1.uniqueColumn)

Edit: as target causes an error so i removed it
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 12:58:18

or use left join

insert database1.dbo.table1 as target
select * from database1.dbo.table1 t
left join database1.dbo.table1 t1 
on t1.uniqueColumn = t.uniqueColumn
where t1.uniqueColumn  is null
and ... (other conditions)

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 13:35:41

I now have this

INSERT default_nsp_db.td.REQ_TRACE
SELECT * FROM old_nsp.td.REQ_TRACE
WHERE NOT EXISTS (SELECT 1 FROM default_nsp_db.td.REQ_TRACE t1 WHERE t1.RT_TRACE_ID = old_nsp.td.REQ_TRACE.RT_TRACE_ID)

and get the message

"Cannot insert duplicate key row in object 'td.REQ_TRACE' with unique index 'RT_FROM_TO_IDX'.
The statement has been terminated."

I have a column named 'RT_FROM_TO_ID' but no 'RT_FROM_TO_IDX'. Is it 'RT_FROM_TO_ID' they mean?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 13:39:19

Yes.
'RT_FROM_TO_IDX' is the name of the index.

No, you're never too old to Yak'n'Roll if you're too young to die.

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 13:44:32

To be honest I am not sure what an index is. Is that not only a column holding a unique value, RT_TRACE_ID in my case? Is there a 'RT_FROM_TO_IDX' column I cannot see?

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 13:47:40

Ah, I found it under "Indexes" for the table together with three other. What do they mean and what is the problem here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 13:47:59

quote:
Originally posted by nasu

To be honest I am not sure what an index is. Is that not only a column holding a unique value, RT_TRACE_ID in my case? Is there a 'RT_FROM_TO_IDX' column I cannot see?

find out the column on which index exists and make sure you dont insert duplicate values into column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 13:49:24

A unique index created on column requirs that column should hold only unique value so whenever you try to insert a value to column which already exists it will throw above error.

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-22 : 14:00:32

Now I was able to open properties for this index and I can see that two fields, RT_FROM_REQ_ID and RT_TO_REQ_ID are mentioned. Does this mean that the combination of these two fields must be unique?

How would I change my query to check that the combination of these two fields does not already exist?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 16:35:24

Try this:


INSERT default_nsp_db.td.REQ_TRACE
SELECT * FROM old_nsp.td.REQ_TRACE 
WHERE NOT EXISTS (SELECT 1 FROM default_nsp_db.td.REQ_TRACE t1 
                  WHERE t1.RT_FROM_REQ_ID = old_nsp.td.RT_FROM_REQ_ID 
                    AND t1.RT_TO_REQ_ID  = old_nsp.td.RT_TO_REQ_ID )

Webfred
EDIT: yes - this does mean that the combination of these two fields must be unique.

No, you're never too old to Yak'n'Roll if you're too young to die.

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-23 : 02:34:51

I now get this message.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "old_nsp.td.RT_FROM_REQ_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "old_nsp.td.RT_TO_REQ_ID" could not be bound.

Can aynone explain to me what the sub query (it is a sub query, right?) below does?

SELECT 1 FROM default_nsp_db.td.REQ_TRACE t1
WHERE t1.RT_FROM_REQ_ID = old_nsp.td.RT_FROM_REQ_ID
AND t1.RT_TO_REQ_ID = old_nsp.td.RT_TO_REQ_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 03:04:35

quote:
Originally posted by nasu

I now get this message.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "old_nsp.td.RT_FROM_REQ_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "old_nsp.td.RT_TO_REQ_ID" could not be bound.

Can aynone explain to me what the sub query (it is a sub query, right?) below does?

SELECT 1 FROM default_nsp_db.td.REQ_TRACE t1
WHERE t1.RT_FROM_REQ_ID = old_nsp.td.RT_FROM_REQ_ID
AND t1.RT_TO_REQ_ID = old_nsp.td.RT_TO_REQ_ID

try this

INSERT default_nsp_db.td.REQ_TRACE
SELECT * FROM old_nsp.td.REQ_TRACE o 
WHERE NOT EXISTS (SELECT 1 FROM default_nsp_db.td.REQ_TRACE t1 
                  WHERE t1.RT_FROM_REQ_ID = o.RT_FROM_REQ_ID 
                    AND t1.RT_TO_REQ_ID  = o.RT_TO_REQ_ID )

what the subquery does is to return a boolean 1 if records exists on default_nsp_db.td.REQ_TRACE table with values for RT_FROM_REQ_ID and RT_TO_REQ_ID same as that in old_nsp.td.REQ_TRACE table and if not boolean 0. WHERE NOT EXISTS returns true if no such record exists. so in effect what you're doing is returning only records from old_nsp.td.REQ_TRACE which have values not existing in default_nsp_db.td.REQ_TRACE

nasu
Yak Posting Veteran

50 Posts

Posted - 2008-10-23 : 03:57:04

It now works. A million thanks to webfred and visakh16 for your fast replies. You saved me! I will immediately start learning SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 03:59:35

quote:
Originally posted by nasu

It now works. A million thanks to webfred and visakh16 for your fast replies. You saved me! I will immediately start learning SQL.

no worries

Any doubts you face feel free to post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources