Use a hidden field rather than a label, labels are not submitted with a form so you can't pass their data to your server-side code.
<script type="text/javascript">
function myFunction() {
var person = prompt("Enter Table Name");
if (person != null) {
document.getElementById('hiddenName').value = person;
}
}
</script>
<input type="hidden" name="hiddenName" id="hiddenName" />
<asp:Button ID="Button2" ValidationGroup="p" runat="server" OnClick="insert" OnClientClick="javascript:myFunction()" Text="Create Table" />
Code behind
protected void insert(object sender, EventArgs e)
{
string value = Request.Form["hiddenName"];
}