April 28, 2009 at 11:41 am
If I have a series of string values such as '12345' '678910' '12,13,14,15,5'
and I want to insert them into a temp table using only Tsql, how would I do that? I played around with select into with no luck. I don't want to run bulk insert, SSIS or use the data import wizard. I can create the temp table e.g.
create table #temp1 ( myfield varchar(30)) no problem.
I'm hoping to avoid having to create hundreds of statements like:
insert into #temp1(myfield) values ('12345')
insert into #temp1(myfield) values ('56789') etc
April 28, 2009 at 12:00 pm
insert into #temp1(myfield)
select myfield from #temp1
--edit
disregard this post. I posted after reading roughly 2/3 of the question. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2009 at 12:12 pm
How are the original values stored? Is it a text file? A spreadsheet? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 12:34 pm
I may get the values in a spreadsheet or text file but was trying to avoid using any of the tools such as BCP, SSIS or the data import wizard. So let's assume I've pasted 100 such string values into a management studio query window. Like this:
'123345','56432','99555','112244'
April 28, 2009 at 12:38 pm
if you are going to pull them from a spreadsheet you should look at the concatenation function. you could use this to create your insert statement before you bring it over.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2009 at 12:46 pm
Concatenation can be handy if you're pulling from a spreadsheet. I've had to do that with small data sets. Just a =Concatenate("Select '", A1, "', '", B1, "' union all") will give you:
Select 'text', 'text' union all
Select 'text', 'text' unon all
Cut and paste that into SSMS with the rest of your insert statement, remove the last "all" from your selects and you're good to go.
Takes a few seconds to set up.
-- Kit
April 28, 2009 at 12:47 pm
Does something like this help?
set nocount on;
declare @String varchar(max);
select @String = '123345,56432,99555,112244' ;
insert into Etemp1(myfield)
select substring(@String + ',', Number, charindex(',', @String + ',', Number) - Number)
from dbo.Numbers
where Number <= len(@String)
and substring(',' + @String, Number, 1) = ','
order by Number;
It requires that you have a Numbers table, or some other table with sequential integers in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2009 at 2:44 pm
Yes thanks that worked. I used a while loop with counter to populate a number table. Initially I only put 3000 records in it even though the length of my string of values was over 30,000 characters. Didn't work completely so I repopulated the number table with 50k records, then it worked.
GSquared (4/28/2009)
Does something like this help?
set nocount on;declare @String varchar(max);
select @String = '123345,56432,99555,112244' ;
insert into Etemp1(myfield)
select substring(@String + ',', Number, charindex(',', @String + ',', Number) - Number)
from dbo.Numbers
where Number <= len(@String)
and substring(',' + @String, Number, 1) = ','
order by Number;
It requires that you have a Numbers table, or some other table with sequential integers in it.
April 29, 2009 at 7:28 am
Glad we could help. You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 8:02 am
I get a lot of requests from customer reps where they send me a spreadsheet with hundreds or thousands of values ( motor vehicle vin numbers generally ) and they want a left join with their list against a production table so they can see nulls where there is no match. Thus the need to get the vins into a temp table. If it weren't for the need to join I could paste the values form Excel into a text editor like Editplus and do a search and replace on carriage returns changing them to ',' to give me the big string to use where vin in('vin1','vin2',' etc
Your approach eliminates the need to load the spreadsheet.
April 29, 2009 at 8:08 am
In that case, you might want to take a look at OpenRowset. With that, you can query Excel directly, as if the spreadsheet were a table. That might be even better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply