SQLTeam.com | Weblogs | Forums

How to "step through" a list of values creating a record for each value

sql2012

#1

Hi, using SQL server 2012. I need to run this code and somehow STEP through a list of different "IDVAR" values I have as text in a list so that it creates a record in TableA from a pre-existing record in TableB with Record_id = 29, adding its value and a "0" to a certain fields for each value:

Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
IDVAR(my text list of different values), 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29

I have used "IN" clauses in the past to do similar things (aka like "AND T1.record_id IN(100,200...my list of values)" to do similar things, but can't figure out how to do this here.

Any help appreciated!

Thanks!


#2

Post create table script and inserts with sample data.


#3

Hi, what I am trying to do is automate the following with a list of values I have that are in a spreadsheet. So if doing it manually it would look like this:

For first value in list which is "100":

Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
100, 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29

For my 2nd value in the list which is "200":

Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
200, 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29

where 100 and 200 are the first 2 values in my list. but I have over 3000 so I need to automate the process and have the code iterate through my values like an "IN clause" would do like "...AND t.record_id IN(100,200,...)"

However this does not work with my INSERT statement for some reason.

I can post all the create table data but both tables already exist and I didn't think it viable as there are a ton of columns. (i just listed the first few values for the example)

How can I automate this and have the code cycle through all of my list values?

Thanks,

MP


#4

Without have available data - it is very hard to test and validate a solution:

Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
t.Value, 0, [t1], [t2], .....etc)
from
tableB
CROSS JOIN TextList t
where
tableB.record_id = 29

If you have everything already in a table - then cross join to that table for the one row you want to use as the source value. Test using a SELECT statement before running the insert to insure you are getting the right values and number of rows.


#5

Ok, great, I will try this.

Thank you!