I was asked in the interview tell me the different ways of exporting database from one sql server to another, I knew only about creating a .bak file and then restoring it to another sql server which I told them. However, they asked me about a single SQL INSERT command which will perform this task.
I have googled it and can not find it. Please tell me if there is any such command ?
I have never heard of such a command and this is the MS support article that tells you how to move database between servers. It gives three options none of which are a single insert statement, the closest is using sp_detach_db and sp_attach_db.
Well with a SQL Statement you can do a backup and a restore. Doing it with one SQL INSERT... I've never heard something like this. Maybe one table. But not the whole database.
The other way would be to use the "Copy Database Wizzard".
I am doing also interviews and sometimes you just ask stuff that does not exist or does not work and see what is happening.
If you had a linked server already, I would guess you could use sp_msforeachtable around an INSERT INTO server2.tbl SELECT * FROM tbl.
But that's not going to handle referential integrity order dependencies or scenarios where you might need IDENTITY INSERT, disabling triggers or whatever. Handling trivial cases is usually, by definition, trivial.