T-SQL insert bulk text out from Select

问题:

In SQL server 2008, I have select statement that spits below text output.

'text1d','text2','text3'

'text3d','text2','text6'

'text1d','text2','text6'

'text18d','text2','text3

'text15','text2','text5'

.....

.....

I want to insert above output into Test1 table.

create table Test1  (c1 varchar(20),c2 varchar(20),c3 varchar(20))

I can modify select script that generates above output with something else instead of 'single quote' and 'comma' if it helps.

Thanks.

回答1:

Not very optimized, but probably working:

DECLARE @values VARCHAR(MAX)
DECLARE c_select CURSOR FAST_FORWARD FOR
SELECT <your statement here>
OPEN c_select
FETCH NEXT FROM c_select INTO @values
WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC('INSERT INTO table2 (col1, col2, col3) VALUES (' + @values + ')')
   FETCH NEXT FROM c_select INTO @values
END
CLOSE c_select
DEALLOCATE c_select

回答2:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"

回答3:

Why combine the columns into a delimited string just to parse it out again for the insert?

Can't you just:

insert into Test1
    (c1, c2, c3)
    <variation of your original select>

回答4:

You can make your regexp func return an xml. Instead of this 'text1d','text2','text3' you can build this <v>text1d</v><v>text2</v><v>text3</v>.

Here is a sample with a table variable simulating your query.

Setup test data

declare @T table (txt varchar(50))

insert into @T values ('<v>text1d</v><v>text2</v><v>text3</v>')
insert into @T values ('<v>text3d</v><v>text2</v><v>text6</v>')
insert into @T values ('<v>text1d</v><v>text2</v><v>text6</v>')
insert into @T values ('<v>text18d</v><v>text2</v><v>tex3t</v>')
insert into @T values ('<v>text15</v><v>text2</v><v>text5</v>')

Your query should return what now is in @T

txt
<v>text1d</v><v>text2</v><v>text3</v>
<v>text3d</v><v>text2</v><v>text6</v>
<v>text1d</v><v>text2</v><v>text6</v>
<v>text18d</v><v>text2</v><v>tex3t</v>
<v>text15</v><v>text2</v><v>text5</v>

Here is the insert statement that queries the xml in @T.txt for the columns.

insert into Test1 (c1, c2, c3)
select
  cast(txt as xml).value('v[1]', 'varchar(20)'),
  cast(txt as xml).value('v[2]', 'varchar(20)'),
  cast(txt as xml).value('v[3]', 'varchar(20)')
from @T 

A query against Test1 give you this result.

c1      c2    c3
text1d  text2 text3
text3d  text2 text6
text1d  text2 text6
text18d text2 tex3t
text15  text2 text5

I guess this is also the answer to this question. Insert into SQL server from Regular Expression returned text