declare @array varchar(2000)set @array='321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK'--Loop until all the values in @arrayinsert into myTable(Col1,Col2,Col3)select @array --only 3digits must be selected,col2,col3 from myOtherTable
USE TempDB --DB that everyone has where we can cause no harm SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed--=============================================================================-- Create and populate a Tally table--=============================================================================--===== Conditionally drop IF OBJECT_ID('dbo.Tally') IS NOT NULL DROP TABLE dbo.Tally--===== Create and populate the Tally table on the fly SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100--===== Let the public use it GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
create function [dbo].[fn_split]( @str varchar(8000), @spliter char(1) ) returns @returnTable table (idx int primary key identity, item varchar(8000)) as begin declare @spliterIndex int select @str = @str + @spliter SELECT @str = @spliter + @str + @spliter INSERT @returnTable SELECT SUBSTRING(@str,N+1,CHARINDEX(@spliter,@str,N+1)-N-1) FROM dbo.Tally WHERE N < LEN(@str) AND SUBSTRING(@str,N,1) = @spliter ORDER BY N returnend
SELECT * FROM fn_split('321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK')
DECLARE @input VARCHAR(2002)DECLARE @array VARCHAR(2000)DECLARE @sepchar CHAR(1)set @sepchar = ',' -- separation character is a commaset @array= '321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK'set @input = @sepchar + @array + @sepchar -- begin and end with separation characters;WITH tally (N) as (SELECT TOP 1000000 row_number() OVER (ORDER BY sc1.id) FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2),ArrayAsTable AS(SELECT substring(@input,N+1,CHARindex(@sepchar,@input,N+1)-(N+1)) as element FROM tally WHERE substring(@input,N,1) = @sepchar and N < len(@input))-- INSERT INTO MyTable (col1,col2,col3)SELECT Element FROM ArrayAsTable-- JOIN MyOtherTable ON (whatever)