I have below text file having different words inside it:
My aim is to insert only 4 character words from the text file into a table variable which is @temp, using bcp command.
So, at the end, the table variable @temp will look like below:
-
1Why would you want to put them into a variable? That would only persist for the duration of the BCP command; making the entire task pointless. If you're using BCP, you need to put the data into an actual table, not a variable.Thom A– Thom A ♦2018-10-20 15:17:04 +00:00Commented Oct 20, 2018 at 15:17
-
1You can't - nor would it be useful. A table variable has too limited a scope and lifetime. So rather than discuss the problems with the path you have chosen, discuss your ultimate goal. What do you intend to do with this table once populated?SMor– SMor2018-10-20 15:17:42 +00:00Commented Oct 20, 2018 at 15:17
-
@Larnu I want to apply further logic on the data taken in the table variable and then final result set will be written to another text file, result.txt. Thus, I do not need the table variable after the final sorted data is written in another text file, result.txt. Also, if I use actual table then every time before loading the test.txt in to actual table, I will have to empty the table ( in case test.txt contains different words ) and then load the 4 character words in it and then apply the logic described above. Is it for sure that, in this case, table variable can not be used ?Sweety– Sweety2018-10-20 16:28:51 +00:00Commented Oct 20, 2018 at 16:28
-
@SMor I want to apply further logic on the data taken in the table variable and then final result set will be written to another text file, result.txt. Thus, I do not need the table variable after the final sorted data is written in another text file, result.txt. Also, if I use actual table then every time before loading the test.txt in to actual table, I will have to empty the table ( in case test.txt contains different words ) and then load the 4 character words in it and then apply the logic described above. Is it for sure that, in this case, table variable can not be used ?Sweety– Sweety2018-10-20 16:29:09 +00:00Commented Oct 20, 2018 at 16:29
-
1This sounds like you need to use a staging table. For the reasons both @SMor and I have given, a variable is not the right choice here as it won't persist once the BCP has completed (which means it won't exist when you try to apply further logic to it). A variable only exists within the batch that declared it. As soon as that batch ends, so does the existence of the variable.Thom A– Thom A ♦2018-10-20 16:46:09 +00:00Commented Oct 20, 2018 at 16:46
|
Show 2 more comments
1 Answer
Create a table where you will store the data coming from your file:
create table import(WORDS nvarchar(100))Import data from file with bcp into the table created in the first step:
bcp [test].[dbo].[import] in d:\test.txt -c -TDeclare
@tablevariable:declare @table table ([ID] int identity(1,1), WORDS nvarchar(100))Insert into
@tablevariable only words with length = 4:insert into @table select WORDS from import where len(WORDS) <= 4
Now @table variable contains this data:
