September 14, 2020 at 11:11 pm
If you really want help, help us help you.
Post TEXT, not pictures. And if you're stuck on something, post what you tried. We're not here to do your homework. Ideally, post the following:
1. create table script(s) so we know what tables and data you're using (doesn't have to be real data, just representatitve... but he data structure helps a lot).
2. INSERT scripts to populate the table(s).
3. the output you expected, or are trying to get.
September 15, 2020 at 12:52 am
Hi
Data structure is already there in the picture.
September 15, 2020 at 3:24 am
When I click on the picture, I can't copy it into SSMS. So it won't run. So I can't help. Sorry!
You really need to read this article:
Forum Etiquette: How to post data/code on a forum to get the best help
Please read it and follow the instructions. If you don't post consumable data, it's going to be a cold day in hell before you get an answer.
September 15, 2020 at 3:31 am
I need to verify these procedures if its right or not ?
Can you check on this ?
I need to write a procedure so that when I have to update the table for
more than one Product Type at once, I only have to plug in the table
name and the multiple values and it generates an insert script :
CREATE PROCEDURE dbo.INS_MULTIPLE_PRODUCT_TYPE
@pstrSchemaName
VARCHAR(200)
,@pstrTable
VARCHAR(200)
@pstrColumnValues
VARCHAR(MAX) -- comma separated column values to
be inserted are given in the correct orderAS
BEGIN
DECLARE @index int,
@INSQUERY VARCHAR(MAX),
@Delimiter VARCHAR(2) = ',',
@Total_Rows INT,
@Counter INT = 1,
@productType VARCHAR(255),
@InputText VARCHAR(MAX),
@ColumnValuesText VARCHAR(MAX) = ''
DECLARE @OutputTable TABLE
(
pos INT IDENTITY PRIMARY KEY,
val VARCHAR(4000)
)
SET @index = -1;
SET @InputText= @pstrColumnValues
WHILE (LEN(@InputText) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @InputText)
IF (@index = 0) AND (LEN(@InputText) > 0)
BEGIN
INSERT INTO @OutputTable VALUES (@InputText)BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @OutputTable VALUES (LEFT(@InputText, @index - 1))
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
ELSE
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
SELECT @Total_Rows= Count(1) FROM @OutputTable
WHILE (@Counter<=@Total_Rows )
BEGIN
SELECT @productType = val
FROM @OutputTable
WHERE pos=@Counter
IF @ColumnValuesText = ''
BEGIN
SET @ColumnValuesText = ''''+@productType+''''
END
ELSE
BEGIN
SET @ColumnValuesText = @ColumnValuesText + ',' +
''''+@productType+''''
END
SET @Counter=@Counter+1
End
SELECT @INSQUERY= '''INSERT INTO '+@pstrSchemaName+'.'+@pstrTable +'
Values( '+ @ColumnValuesText+ ')'
SELECT @INSQUERY AS 'INSERT QUERY'
print(@INSQUERY)
END
8) I need to modify my procedure so that it takes the names of columns
that have multiple values and generates an insert script. :
ALTER PROCEDURE dbo.INS_MULTIPLE_PRODUCT_TYPE
@pstrSchemaName
VARCHAR(200)
,@pstrTable
VARCHAR(200)
,@pstrColumnNames
VARCHAR(MAX) -- comma separated column names
,@pstrColumnValues
VARCHAR(MAX) -- comma separated column values to
be inserted are given in the correct order
AS
BEGIN
DECLARE @index int,
@INSQUERY VARCHAR(MAX),
@Delimiter VARCHAR(2) = ',',
@Total_Rows INT,
@Total_Rows_Columns INT,
@Counter INT = 1,
@productType VARCHAR(255),
@InputText VARCHAR(MAX),
@ColumnValuesText VARCHAR(MAX) = '',
@ColumnNamesText VARCHAR(MAX) = ''
DECLARE @OutputTable TABLE
(
pos INT IDENTITY PRIMARY KEY,
val VARCHAR(4000)
)
DECLARE @OutputTableColumns TABLE
(
pos INT IDENTITY PRIMARY KEY,
val VARCHAR(4000)
)
SET @index = -1;
SET @InputText= @pstrColumnValues
WHILE (LEN(@InputText) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @InputText)
IF (@index = 0) AND (LEN(@InputText) > 0)
BEGIN
INSERT INTO @OutputTable VALUES (@InputText)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @OutputTable VALUES (LEFT(@InputText, @index - 1))
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
ELSE
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
SELECT @Total_Rows= Count(1) FROM @OutputTable
WHILE (@Counter<=@Total_Rows )
BEGIN
SELECT @productType = val
FROM @OutputTable
WHERE pos=@Counter
IF @ColumnValuesText = ''
BEGIN
SET @ColumnValuesText = ''''+@productType+''''
END
ELSE
BEGIN
SET @ColumnValuesText = @ColumnValuesText + ',' +
''''+@productType+''''
END
SET @Counter=@Counter+1
End
SET @index = -1;
SET @InputText= @pstrColumnNames;
SET @Counter = 1;
WHILE (LEN(@InputText) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @InputText)
IF (@index = 0) AND (LEN(@InputText) > 0)
BEGIN
INSERT INTO @OutputTableColumns VALUES (@InputText)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @OutputTableColumns VALUES (LEFT(@InputText, @index
- 1))
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
ELSE
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
Select @Total_Rows_Columns = Count(1) FROM @OutputTableColumns
WHILE (@Counter<=@Total_Rows_Columns )
BEGIN
SELECT @productType = val
FROM @OutputTableColumns
WHERE pos=@Counter
IF @ColumnNamesText = ''
BEGIN
SET @ColumnNamesText = @productType
END
ELSE
BEGIN
SET @ColumnNamesText = @ColumnNamesText + ',' +
@productType
END
SET @Counter=@Counter+1
End
SELECT @INSQUERY= '''INSERT INTO '+@pstrSchemaName+'.'+@pstrTable+
'('+@ColumnNamesText+')' +' Values( '+ @ColumnValuesText+ ')'
SELECT @INSQUERY AS 'INSERT QUERY'
print(@INSQUERY)
END
September 15, 2020 at 11:46 am
In general, what you're doing here is attempting to treat SQL like it's a regular, functional, or object oriented language. It's not. SQL is declarative. Don't attempt to control the flow programmatically. Instead, tell it what you want. I can't tell from the picture above whether you're dealing with one table in your homework or 20. However, the way to write an INSERT statement, as you're trying, is straight forward. One for each table. Not one that deals with all tables. So, twenty tables, twenty insert statements. One table, one insert statement, but that insert statement is declared:
INSERT INTO MyTable
(ID,
SomeValue,
SomeOtherValue)
VALUES
(1,
'Hey',
'Ho')
Or, in the case of a procedure, you define parameters for each distinct column, then use those parameters in the INSERT:
...
VALUES
(@ID,
@SomeValue,
@SomeOtherValue)
And so on from there. Calling the proc, you don't pass it a comma delimited set, you pass in the data needed. Unless, you're writing some kind of import script, but even there, you define the table, not iterate through the code like this.
I'd suggest you go look at the Stairways classes we have above. They'll show you how T-SQL works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2020 at 3:41 pm
I could be wrong but most of the code looks like it takes in some form of CSV and splits it. Since you're using SQL Server 2019, lookup the STRING_SPLIT function. If you need to preserve the order of the elements being split, see the DelimitedSplit8K function at the following article.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply