Importing and Exporting Data
-
Is there any way to transfer data from one production database table to another table of the same production database .
I know i can do it simpley by
insert into tablename select * from tablename.
or i can use dts task to tansfer data.
or i can use bcp utility
i can use bulk insert.
but is there anyother way to tranfer data. so that other user should not come to know.
Ex: if there are 300 concurrent user accessing that table i need to use in datatranfer.
Hope u understood my question
thanx
Killer
-
You'll complete the task quickest by using your first method,
insert into tablename select * from tablename
If this affects your user base unacceptably, schedule the job when users are not connected, or in a low-usage hour in the case of 24/7 applications.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
-
Without having to first script the table creation, this would work... very well, very fast, and without any interference...
SELECT * INTO newtablename FROM oldtablename WITH (NOLOCK)
The only thing it doesn't do is copy indexes...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is there anyother way of doing?apart for all this.
from
Killer
-
Why? What's the problem with that method? The "other user should not come to know"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you're doing a real time copy, then the methods above are your best options. The query given with the lock hint (NOLOCK) prevents your SELECT query from establishing (or waiting on) locks by other processes which are also accessing the table. That's about the best you're going to do real time without some sort of snapshot process, like what exists in SQL Server 2005.
What scenario are you trying to avoid?
K. Brian Kelley
@kbriankelley -
Few days back i was interviewd and was asked a question
the Question is below?
THE DBA need to tranfer data from one table from the database to other table of the same database.But the user should not come to know that datatranfer is taking place.
I told all the option i know (DTS,BCP,BULKUPDATE,Select * into,Insert into etc.) But the interviewer asked me there is another way of doing it.
So i need to know from u guys abut it?
Dear jeff,
When u help someone dont ask why?If am satisfied by ur answer then i will not go further with my question?
and will thank u for ur help.
Hope u understand.
from
Killer
-
Sometimes, as in your case, you have to ask "Why" someone can't use the answer provided because they haven't explained themselves correctly... I imagine you get that alot
Good luck with your other posts... 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
I think in my pervious post i explained the question in much better way.
No i did not get what i need from u all.
from
Killer
-
I'm not sure what the interviewer was getting at then. You can do the SELECT INTO using a (NOLOCK) (or just an INSERT ... SELECT with the (NOLOCK) if the table already exists). That's going to ensure you're not going to interfere with the locking on the table. And except for a possible performance hit, a user is not going to know you're doing this behind the scenes.
K. Brian Kelley
@kbriankelley -
Thanks Bkelley .
from
Killer
-
I interpret the interviewer's question in two possible ways... w/o being in front of the interviewer I can't ask clarifying questions so this is what I would assume he/she was referring to.
1. If the goal is to make sure the user is not aware of the transfer, I can only guess the issue they are solving is that the data in table A (Source) needs to be immediately available in Table B (Destination). Probably the inteviewer was looking for the answer of "FOR INSERT UPDATE DELETE TRIGGER". Although the other methods work, this is the only other one I can think of.
2. Since the question and "Trigger" answer are loosley connected and don't necessarily fit, it is possible, and more likely that your interviewer wanted to test how you handled situations where you needed to probe for additional details and if you were willing to admit that you didn't know. Alot of interviewers, if they are good, will keep asking you questions till you fail... this helps them guage where your knowledge stops.
btw: I don't understand why you would tell Jeff not ask "Why?" It is a question that I would have asked the interviewer to further understand what they were meaning. Understanding why a requirment exists makes the difference between a coder and developer. Also, "Why?" would help the readers of this post give you better answers.
-
-
Hi Jason,
In my first post i had wrote that what i used to tranfer data from one table to other. TW i asked him not to ask "why"
When we face a interview we try to give all possible answer to the interviewer which we faced in our previous and present jobs.
But if the interviewer ask the question like this then what u will answer.
There is a table A in DB sales and a table B in DB sales.Table a is being accessed by more then 300 users (24*7) mode.I need to tranfer the data of Table A into Table B without troubling the connected users.
The size of the table is 16 GB.
Table B is null before the datatansfer.
(I used lot of ways to tanfer data and nealy i explained all those ways to the interviewer but he was not satisfied by my answer).
I also explained the one that jeff wrote.
Can u pls shed more light on this.
I already thanked Bkelley for his precious time and help.
The explanation given by bkelly is nice which cleared my confusion.
Thanks once again to Bkelley.
Regards
Killer
-
It's apparent, now, the interviewer was asking the interviewee to think outside the box. The original poster didn't explain it was an interview question until after I asked "why". For the record, there are at least four other methods where a table could be copied without anyone elses knowledge unless extraordinary methods are taken. Perhaps now the poster understands why I asked "why".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff ,
Is it so important to write that question i am asking was asked to me in an interview..
Is this makes a diffrence in my question?
from
Killer
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply