February 14, 2017 at 1:25 pm
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!
February 14, 2017 at 2:27 pm
mpsqlsercen - Tuesday, February 14, 2017 1:25 PMHi, 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 = 29I 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!
Try this on for size:
DECLARE @tableA AS TABLE (
IDVAR varchar(20),
m_id int,
seq int,
t1 int,
t2 int,
record_id int
);
DECLARE @tableB AS TABLE (
m_id int,
seq int,
t1 int,
t2 int,
record_id int
);
INSERT INTO @tableB (m_id, seq, t1, t2, record_id)
SELECT 1, 1, 1, 1, 29;
INSERT INTO @tableA (IDVAR, m_id, seq, [t1], [t2], record_id)
SELECT CA.IDVAR, 1, 0, B.[t1], B.[t2], B.record_id
FROM @tableB AS B
CROSS APPLY (
SELECT 'VALUE 1' AS IDVAR UNION ALL
SELECT 'VALUE 2' UNION ALL
SELECT 'VALUE 3'
) AS CA
WHERE B.record_id = 29;
SELECT *
FROM @tableA;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 7:13 am
Hi Steve, thanks for the reply. I am not understanding how CROSS APPLY would work here for inserting my list of values? I have my list of values in an excel list, not in a table, and wanted to just inject them in the code itself like an "IN clause" would do.
The tables already exist and TableB has what I call "template records" in it, that the code below will pluck out of that table, and simply update the M_id and seq fields the numberic values when the records are created in TableA.
To get my desired result manually I would simply run the code like this:
For my first value:
Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
100, 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29
Form my 2nd value:Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
200, 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29
....etc....for all my values in my list, that look like this:
100
200
300...etc
but I can't write all that code for over 3000 records.
With an IN clause I would simply add a line of code like "and T.record_id IN (100,200,...) to do this but it does not work with my INSERT.
I'm not sure how to accomplish this.
Thanks,
MP
February 15, 2017 at 7:41 am
mpsqlsercen - Wednesday, February 15, 2017 7:13 AMHi Steve, thanks for the reply. I am not understanding how CROSS APPLY would work here for inserting my list of values? I have my list of values in an excel list, not in a table, and wanted to just inject them in the code itself like an "IN clause" would do.The tables already exist and TableB has what I call "template records" in it, that the code below will pluck out of that table, and simply update the M_id and seq fields the numberic values when the records are created in TableA.
To get my desired result manually I would simply run the code like this:
For my first value:
Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
100, 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29Form my 2nd value:
Insert Into tableA (m_id,seq, [t1], [t2], .....etc)
select
200, 0, [t1], [t2], .....etc)
from
tableB
where
tableB.record_id = 29....etc....for all my values in my list, that look like this:
100
200
300...etc
but I can't write all that code for over 3000 records.With an IN clause I would simply add a line of code like "and T.record_id IN (100,200,...) to do this but it does not work with my INSERT.
I'm not sure how to accomplish this.
Thanks,
MP
I'm at a loss to understand how the CROSS APPLY fails to operate. However, given that I now know that you have over 3,000 values to work with, that's a whole different ball game in terms of what is necessary. The question is, while you already have the source table and the target table, where, exactly, are all the new values you need to work with? Do you have the ability to generate a comma separated list of all 3,000 or however many? No matter what you do, you'll need a method to turn each value into a single record, so if you can provide a comma-delimited list, then we can employ Jeff Moden's string splitter function. Let me know where the existing values are first and then we'll worry about the methodology in more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 8:24 am
Steve,
I have the list as "text" values, currently in a spreadsheet. Yes, I can convert them to a comma separated list in excel (as 100,200,300...etc), as text to insert into SQL code. I have done similar with an IN clause in the past, taking the values and adding a "," to them and then pasting them into SQL. The IN clause works with using SET, but not with an INSERT INTO.
So, my list of values are currently a comma separated text that I can copy and paste into SQL.
Thanks,
MP
February 15, 2017 at 9:11 am
mpsqlsercen - Wednesday, February 15, 2017 8:24 AMSteve,I have the list as "text" values, currently in a spreadsheet. Yes, I can convert them to a comma separated list in excel (as 100,200,300...etc), as text to insert into SQL code. I have done similar with an IN clause in the past, taking the values and adding a "," to them and then pasting them into SQL. The IN clause works with using SET, but not with an INSERT INTO.
So, my list of values are currently a comma separated text that I can copy and paste into SQL.
Thanks,
MP
At the moment, I'm searching for a string splitter for large strings... those in excess of 8,000 characters total length, including all the commas. Alternatively, might you be able to do batches at a time? If you can keep the total string length to 8,000 or less for each batch, we can use the function I already know exists. If so, here's the how:
DECLARE @LIST AS varchar(8000) = '100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270';
DECLARE @tableA AS TABLE (
m_id int,
seq int,
t1 int,
t2 int,
record_id int
);
DECLARE @tableB AS TABLE (
m_id int,
seq int,
t1 int,
t2 int,
record_id int
);
INSERT INTO @tableB (m_id, seq, t1, t2, record_id)
SELECT 1, 1, 1, 1, 29;
INSERT INTO @tableA (m_id, seq, [t1], [t2], record_id)
SELECT S.Item, 1, B.[t1], B.[t2], B.record_id
FROM @tableB AS B
CROSS APPLY dbo.DelimitedSplit8K(@LIST, ',') AS S
WHERE B.record_id = 29;
SELECT *
FROM @tableA;
The hard part is going to be remembering to keep it to 8,000 or less, and you can NOT get away with an oops.... A longer string might either get truncated, or it might cause an error. Correct results is the one thing it won't do under those circumstances. You can find the code for Jeff's function here:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 9:20 am
Steve,
I will give this a try, and yes, I could do it in batches, thanks! I did not realize there were limitations in the number of characters that can be used here.
If I added the list of values I have to a SQL table, could this be avoided and run some other way?
Thanks,
MP
February 15, 2017 at 10:33 am
MP, is this a one time import of the data in your spreadsheet? Or will it be an ongoing import that needs to be automated?
February 15, 2017 at 10:53 am
mpsqlsercen - Wednesday, February 15, 2017 9:20 AMSteve,I will give this a try, and yes, I could do it in batches, thanks! I did not realize there were limitations in the number of characters that can be used here.
If I added the list of values I have to a SQL table, could this be avoided and run some other way?
Thanks,
MP
Well, the only thing it wouldn't need is the splitter function. In the last query I supplied, just substitute in your table name where the dbo.DelimitedSplit8K function appears, and replace S.Item with "S." and the field name from your table that holds the list values. You do have the list values in individual rows, right?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 10:55 am
Also, to Jeff Atherton's point, is this a one-time thing or an ongoing need? The reason is because there are some great ways to automate this process, involving potentially SSIS and a SQL Agent job, but it would require that steps be added to ensure the process is accurate and repeatable, and errors can be detected.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 11:30 am
mpsqlsercen - Wednesday, February 15, 2017 9:20 AMSteve,I will give this a try, and yes, I could do it in batches, thanks! I did not realize there were limitations in the number of characters that can be used here.
If I added the list of values I have to a SQL table, could this be avoided and run some other way?
Thanks,
MP
Just an FYI, there's not a limitation on the size of a string you can have, it's just the "easy" way to split the string has the limitation of 8,000 characters. Given that you already have a way to turn those values into records in a SQL Server table, that issue is effectively moot, as that's all the string splitter was going to do anyway.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 10:03 pm
Why not just load the ACE drivers and read directly from the spreadsheet?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2017 at 7:15 am
Hi,
Yes, this is just a one time deal that does not need to be repeated.
Thanks,
MP
February 16, 2017 at 7:38 am
sgmunson - Wednesday, February 15, 2017 10:53 AMmpsqlsercen - Wednesday, February 15, 2017 9:20 AMSteve,I will give this a try, and yes, I could do it in batches, thanks! I did not realize there were limitations in the number of characters that can be used here.
If I added the list of values I have to a SQL table, could this be avoided and run some other way?
Thanks,
MP
Well, the only thing it wouldn't need is the splitter function. In the last query I supplied, just substitute in your table name where the dbo.DelimitedSplit8K function appears, and replace S.Item with "S." and the field name from your table that holds the list values. You do have the list values in individual rows, right?
Steve,
Yes, if I put them in a table it would be one value per row.
I'll give this a try and let you know my results.
Thanks for the help!
February 16, 2017 at 7:41 am
Jeff Moden - Wednesday, February 15, 2017 10:03 PMWhy not just load the ACE drivers and read directly from the spreadsheet?
Jeff,
Sorry, I'm not even sure what ACE is actually. I have done a couple SSIS things before but I was looking for a quick fix as I'm not a pro at SSIS and it would take me along time to get it rolled out.
I appreciate the idea however!
Thanks!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply