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:
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.
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?
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.