SQL Server Performance Forum – Threads Archive

Script a table with data

I want to script a table with its data. How can I do that?
CanadaDBA

---

http://www.nigelrivett.net/sp_CreateDataLoadScript.html for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thank you Satya, Perfect solution. Using that, I could write a script to generate data load for all tables of a DB.

CanadaDBA

---

…just for completeness:http://www.karaszi.com/SQLServer/info_generate_script.asp –Frank
http://www.insidesql.de

---

Satya, At the bottom of the script there is a exec (@cmd1 + @cmd2) that only shows 255 charcters while (@cmd1 + @cmd2) length is more than 255. How can I get the whole string? thanks,

CanadaDBA

---

Thanks Frank, It seems good and useful site.
CanadaDBA

---

Yup, it is. And it’s also run by a SQL Server MVP. –Frank
http://www.insidesql.de

---

Frank,
I asked a question from Satya before reply your message. It seems you are on-line, would you check it and answer it if possible? Thanks
CanadaDBA

---

Have you tried executing the SP for a table? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi Satya, Seems you are on-line. Yes, I executed the script and it generates but the lines are truncated. SELECT Len(LTrim(RTrim(@cmd1))) shows 673
SELECT Len(LTrim(RTrim(@cmd2))) shows 5733 but exec (@cmd1 + @cmd2) creates truncated rows. Here is one of the result rows: insert tblActiveBranch ( Branch,ARCPhone,ARCAddress,ARCCity,ARCState,ARCZip,ActiveDate,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,Branch,ARCPhone,ARCAddress,ARCCity,ARCState,ARCZip,ActiveDate,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,Branch,ARCPhone,AR

CanadaDBA

---

Sorry, was on my way home after posting.<br />As for the script:<br />Why don’t you contact Nigel directly? Don’t know if he has an email contact on his site, but you can always meet him on<a target="_blank" href=http://www.sqlteam.com.>http://www.sqlteam.com.</a> I guess no one can better answer your question.<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />

---

I’ve replied before I was leaving office, I agree with Frank to ask Nigel directly.
I never had that problem at my end Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thank you for your replies. That’s very good idea. I’ll send him an email and post the results here.
CanadaDBA

---

]]>