December 28, 2008 at 5:03 am
Hello All ,
I have 2 questions from you , and I think that these are similar to each other from different aspects
At first
How can we insert a big text like an article into a table ,
I dont want to ask about database design , just the insertion is what I'm looking now
Second , I want to do such a thing but inside ssms and
the size isn't matter , just I want to know the best way to insert a multiLine Text into a record ,
I want to not have problems with special characters and also not have problems with line-breaks ,
what can you suggest
please look at this ,
DECLARE @text nvarchar(500)
DECLARE @ID int
SET @ID = 1
SET @text = N
'
jkhsdfhsdjkfsdjfhksdfkshdf
askdjhakjsdhkajsdhkashdkjh
jsadkashdjkasdhjkasdhasdjh
'
/* I know that It doesn't work , but I want to show you what I gonna do
*/
UPDATE SAMPLES
SET Description = @text
WHERE id = @ID
December 28, 2008 at 6:40 am
December 28, 2008 at 6:50 am
You can test here!
CREATE TABLE #TESTING
(
ID INT,
TEXTING NVARCHAR(MAX)
);
GO
INSERT INTO #TESTING
SELECT 1, 'YOUR LONG TEXT MAYBE HERE!';
DECLARE @TEXT AS NVARCHAR(MAX)
DECLARE @ID AS INT
SET @ID = 1
SET @TEXT = ' bLLA BLLA BLLA BLLA BLLA BLLA
LKDJALJGKLJASF
ALDJFLKAJDKLFAJLDF
ASLDJFLKAJSFLAJLKDJASDF
ASDFJLASDFLJALDFA ADF ASDFASDF ASDFASDFWERWER SDFASD MAYBE YOU ARE HAPPY NOW'
UPDATE #TESTING
SET TEXTING = @TEXT
WHERE ID = @ID;
SELECT * FROM #TESTING;
And don't forget that if your table design the column where you want to do update is designed as nvarchar(100) and you are trying to insert or update the text with more than 100 characters it dons't work!
So you should go and change the datatype of that column and the number of characters but the best one is NVARCHAR(MAX) than do the updates with long text as you want!
:w00t:
December 28, 2008 at 7:18 am
Thanks my friend ,
I'll test it ,
but Is it the best way for two questions above ?
is it a tool for doing that the best way ?
are there any good articles to play around about this subject ?
thanks for more information
December 28, 2008 at 8:49 am
I tesed that , It worked ,
but my problem wasn't the nvarchar(Max) ,
the code that you have sent here has some differences from my code ,
you used a TempTable ( I think ) ,
but for me the table exists and I dont want to set all the columns like that and so forth ,
Do I have to make a new table ?
Just I want to send the data to one of the columns
also I want to know about inserting from a text file
thanks again
December 28, 2008 at 9:37 am
I created temp table, 1 row for testing and the code to show you that you can do the update with larg data if you using NVARCHAR(MAX)!
Maybe you should post the structure of the table and some sample data and I hope that we will write here exactly what you want!
December 28, 2008 at 1:07 pm
Dugi , Your help is really appreciated ,
the thing that now I asked you is just to know why my code doesn't work ,
please look at here ,
I used a code like this without temp table ,
I do not insist on this , but I dont know how to use your code inside
this database ,
This is the code :
DECLARE @TEXT AS nvarchar(MAX)
DECLARE @ID AS INT
SET @ID = 1
SET @TEXT = N' bLLA BLLA BLLA BLLA BLLA BLLA
LKDJALJGKLJASF
ALDJFLKAJDKLFAJLDF
ASLDJFLKAJSFLAJLKDJASDF
ASDFJLASDFLJALDFA ADF ASDFASDF ASDFASDFWERWER SDFASD MAYBE YOU ARE HAPPY NOW'
UPDATE Samples
SET Description = @TEXT
WHERE ID = @ID;
SELECT * FROM Samples;
and imagine that , In the Samples Table that we want to use
we have :
id , Image , Description
in every format you like ,
with this structure , how can I use your suggestion
to insert descriptions inside this field , with identifying the desired id ,
thank you my friend 😉
December 28, 2008 at 2:04 pm
This is the reason that I told you to post the table structure and soma sample data and little bit scenario what do you want to do!
Anyway, Update and Insert are different actions, so if you go to update that table it will updated without any problem, but if you go to insert the data in the order as you post ( ID, Image, Description ), instead of Update you should write this:
CREATE TABLE #TESTING
(
ID INT,
TEXTING NVARCHAR(MAX)
);
GO
INSERT INTO #TESTING
SELECT 1, 'YOUR LONG TEXT MAYBE HERE!';
DECLARE @TEXT AS NVARCHAR(MAX)
DECLARE @ID AS INT
SET @ID = 1
SET @TEXT = ' bLLA BLLA BLLA BLLA BLLA BLLA
LKDJALJGKLJASF
ALDJFLKAJDKLFAJLDF
ASLDJFLKAJSFLAJLKDJASDF
ASDFJLASDFLJALDFA ADF ASDFASDF ASDFASDFWERWER SDFASD MAYBE YOU ARE HAPPY NOW'
--Insteda of update you will use INSERT
--UPDATE #TESTING
--SET TEXTING = @TEXT
--WHERE ID = @ID;
INSERT INTO #TESTING
VALUES (@ID, @TEXT);
SELECT * FROM #TESTING;
December 28, 2008 at 2:06 pm
December 28, 2008 at 2:33 pm
feodon (12/28/2008)
Hello All ,I have 2 questions from you , and I think that these are similar to each other from different aspects
At first
How can we insert a big text like an article into a table ,
I dont want to ask about database design , just the insertion is what I'm looking now
Second , I want to do such a thing but inside ssms and
the size isn't matter , just I want to know the best way to insert a multiLine Text into a record ,
I want to not have problems with special characters and also not have problems with line-breaks ,
what can you suggest
please look at this ,
DECLARE @text nvarchar(500)
DECLARE @ID int
SET @ID = 1
SET @text = N
'
jkhsdfhsdjkfsdjfhksdfkshdf
askdjhakjsdhkajsdhkashdkjh
jsadkashdjkasdhjkasdhasdjh
'
/* I know that It doesn't work , but I want to show you what I gonna do
*/
UPDATE SAMPLES
SET Description = @text
WHERE id = @ID
Maybe that didn't work... but this does...
DECLARE @text nvarchar(500)
DECLARE @ID int
SET @ID = 1
SET @text = N'
jkhsdfhsdjkfsdjfhksdfkshdf
askdjhakjsdhkajsdhkashdkjh
jsadkashdjkasdhjkasdhasdjh
'
/* I know that It doesn't work , but I want to show you what I gonna do
*/
UPDATE SAMPLES
SET Description = @text
WHERE id = @ID
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2008 at 3:03 pm
Jeff Moden (12/28/2008)
feodon (12/28/2008)
Hello All ,I have 2 questions from you , and I think that these are similar to each other from different aspects
At first
How can we insert a big text like an article into a table ,
I dont want to ask about database design , just the insertion is what I'm looking now
Second , I want to do such a thing but inside ssms and
the size isn't matter , just I want to know the best way to insert a multiLine Text into a record ,
I want to not have problems with special characters and also not have problems with line-breaks ,
what can you suggest
please look at this ,
DECLARE @text nvarchar(500)
DECLARE @ID int
SET @ID = 1
SET @text = N
'
jkhsdfhsdjkfsdjfhksdfkshdf
askdjhakjsdhkajsdhkashdkjh
jsadkashdjkasdhjkasdhasdjh
'
/* I know that It doesn't work , but I want to show you what I gonna do
*/
UPDATE SAMPLES
SET Description = @text
WHERE id = @ID
Maybe that didn't work... but this does...
DECLARE @text nvarchar(500)
DECLARE @ID int
SET @ID = 1
SET @text = N'
jkhsdfhsdjkfsdjfhksdfkshdf
askdjhakjsdhkajsdhkashdkjh
jsadkashdjkasdhjkasdhasdjh
'
/* I know that It doesn't work , but I want to show you what I gonna do
*/
UPDATE SAMPLES
SET Description = @text
WHERE id = @ID
Sure it will work Jeff, but we didn't know the real problem of feodon! :hehe:
December 28, 2008 at 3:56 pm
Jeff Moden , again you did great ,
the solution seems to be simple ,
just a quotation ,
thanks ,
--
Dugi , I'll print and look at your code and let you know the result ,
thanks for all your help
December 29, 2008 at 2:46 am
Hello Jeff Moden , thanks for your precise look at my code
What if we have a more complicated text ,
It worked with simple texts ,
but when I copied fore example one part of an article on the net and place
that inside quotations , It didn't work ,
maybe that is because of special characters , or line-breaks ,
I dont know , and also dont know the solution that works here ,
Dugi , I printed your post ,
will tell you the result ,
Do you think that my wanted thing isn't clear ?
I want to copy-Paste any part of an article
for example to this SAMPLE Database's Description field ,
the code that Jeff sent here was the simplest form that I want ,
but It doesn't work with texts grabbed from articles and so ,
please look at here :
---------------------------------------
DECLARE @text nvarchar(MAX)
DECLARE @ID int
SET @ID = 1
SET @text = N'
[ ---- These are sample text that I copied --------- ]
At first
How can we insert a big text like an article into a table ,
I dont want to ask about database design , just the insertion is what Im looking now
Second , I want to do such a thing but inside ssms and
the size isnt matter , just I want to know the best way to insert a multiLine Text into a record ,
I want to not have problems with special characters and also not have problems with line-breaks ,
what can you suggest
'
UPDATE SAMPLES
SET Description = @text
WHERE id = @ID
SELECT * FROM SAMPLES
-----------------------------------
It gives me this error :
Msg 8152, Level 16, State 10, Line 17
String or binary data would be truncated.
The statement has been terminated.
December 29, 2008 at 5:10 am
feodon (12/29/2008)
Hello Jeff Moden , thanks for your precise look at my codeWhat if we have a more complicated text ,
It worked with simple texts ,
but when I copied fore example one part of an article on the net and place
that inside quotations , It didn't work ,
maybe that is because of special characters , or line-breaks ,
I dont know , and also dont know the solution that works here ,
If there are any single quotes in the text, that will foul the works up. For example, the contraction "it's" has a single quote in it. Signs of ownership like "Jeff's code" has a single quote in it. All of those must be change to 2 single quotes if you intend to copy and paste.
Personally, I wouldn't do it this way. I'd save the large text in a file and save the file path/name in the database... let the GUI do the work.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2008 at 5:16 am
Or, you can do something similar to what's in the following link...
http://www.mssqltips.com/tip.asp?tip=1643
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply