Need help on SQL script

Question

Need help on SQL script

jds-685721

SSC Enthusiast

Points: 128
More actions
February 3, 2014 at 3:52 pm

#285139

Hello, I am learning SQL and wondering if someone can help me with script below – not sure if I am posting it in correct session
I have a table TBL1 with data like below
Col1,Col2,Col3Col4,Col5
12Val_A,Val_B,Val_C
12Val_DVal_B,Val_E
12…..
22…
22…
Tons of data…
Now I want to read through TBL1 and insert some rows where Col1 =1 with data below
32Val_AVal_BVal_C
32Val_DVal_BVal_C
32….
So basically, I want to duplicate the rows and just replace the first column with new value (replace 1 with 3)
BTW, Col1, COl3, Col4, Col5 are the keys od the table
Thanks in advance
James

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Welcome to the forum and congrats on trying to improve you T-SQL coding ability. Please read the article in my signature and repost you question with the create table statements and sample data inserts along with the desired output. This will help you get a more precise answer.

With that being said you could do the following:

insert TBL1

select 3, Col2, Col3, Col4, Col5

from TBL1

where Col1 = 1

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Are you sure that rows

Col1,Col2,Col3Col4,Col5

32Val_AVal_BVal_C

32Val_DVal_BVal_C

are not already existing rows?

Igor Micev,My blog: www.igormicev.com

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Keith Tate (2/3/2014)
Welcome to the forum and congrats on trying to improve you T-SQL coding ability. Please read the article in my signature and repost you question with the create table statements and sample data inserts along with the desired output. This will help you get a more precise answer.
With that being said you could do the following:
insert TBL1
select 3, Col2, Col3, Col4, Col5
from TBL1
where Col1 = 1

That is basically what I would do as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jds-685721 SSC Enthusiast Points: 128 More actions · Answer 4

Thaks all for the help.

It's works great! Now can I ask more?

Sorry for not adding them all in once - just try to make it simple for myself 🙂

Col1, Col2, Col3 Col4, Col5

1 2 Val_A, Val_B, Val_C

1 2 Val_D Val_B, Val_E

1 2 …..

2 2 …

Now I want to modify the Col2 base on the Col4’s value(Bal_B)

So it would be like this:

1 2 Val_A, Val_B, Val_C

1 2 Val_D Val_B, Val_E

1 2 …..

2 2 …

3,Val_B,Val_A,Val_B,Val_C

3,Val_B,Val_D Val_B,Val_E

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 5

You get one freebie without create table statements and sample data :-D. Please read the article that I mentioned and add the necessary information to the post and someone will be able to answer your question.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

jds-685721 (2/3/2014)
Thaks all for the help.
It's works great! Now can I ask more?
Sorry for not adding them all in once - just try to make it simple for myself 🙂
Col1, Col2, Col3 Col4, Col5
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …
Now I want to modify the Col2 base on the Col4’s value(Bal_B)
So it would be like this:
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …
3,Val_B,Val_A,Val_B,Val_C
3,Val_B,Val_D Val_B,Val_E

Think about the question you are asking and the general tsql constructs. Try to write that out in as close to valid tsql as you can get and post it if it isn't working.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jds-685721 SSC Enthusiast Points: 128 More actions · Answer 7

Here is my revised request again. Please help

CREATE TABLE [dbo].[MYTABLE]

(

[TND_CD] [smallint] NOT NULL,

[ACCPT_FOR_SLS_FG]

[STR_ID] [int] NOT NULL,

[HIER_ID] [int] NOT NULL,

[LOC_ID] [int]NOT NULL

)

CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED

(

[TND_CD] ASC,

[STR_ID] ASC,

[HIER_ID] ASC,

[LOC_ID] ASC

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #MYTABLE ON

INSERT INTO [MYTABLE] ([TND_CD], [ACPT_PYMT_FLG], [STR_ID], [HIER_ID], [LOC_ID])

SELECT '1', '10','0','0','1' UNION ALL

SELECT '1', '10','0','0','2' UNION ALL

SELECT '1', '10','0','0','3' UNION ALL

SELECT '1', '10','0','0','4' UNION ALL

SELECT '1', '10','0','0','5' UNION ALL

SELECT '2', '11','0','0','1' UNION ALL

SELECT '2', '11','0','0','2' UNION ALL

SELECT '2', '11','0','0','3' UNION ALL

SELECT '2', '11','0','0','4' UNION ALL

SELECT '2', '11','0','0','5'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #MYTABLE OFF

-- Get the number of rows in the looping table

DECLARE @RowCount int

SET @RowCount = (Select count(*) FROM [MYTABLE] WHERE TND_CD =1 )

-- Declare an iterator

DECLARE @I int

-- Initialize the iterator

SET @I = 1

Declare @STR_ID int

Declare @HIER_ID int

Declare @LOC_ID int

-- Loop through the rows of a table @MYTABLE

WHILE (@I <= @RowCount)

BEGIN

Select @STR_ID=LOC_ID, @HIER_ID=HIER_ID, @LOC_ID=LOC_ID FROM [MYTABLE] WHERE TND_CD=1

print TND_CD

Print @STR_ID

print @HIER_ID

print @LOC_ID

INSERT INTO [MYTABLER] VALUES(8 ,10,@STR_ID ,@HIER_ID,@LOC_ID)

PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)

SET @I = @I + 1

END

Here is what I am expecting to get:

[TND_CD][ACCPT_FOR_SLS_FG][STR_ID][HIER_ID][LOC_ID]

110001

110002

110003

110004

110005

211001

211002

211003

211004

211005

810101

810202

810303

810404

810505

and this is what error I got:

1

5

10

0

5

Msg 2627, Level 14, State 1, Line 25

Violation of PRIMARY KEY constraint 'PK_TENDER'. Cannot insert duplicate key in object 'dbo.TENDER'.

The statement has been terminated.

Row No = 1

1

5

10

0

5

Msg 2627, Level 14, State 1, Line 25

Violation of PRIMARY KEY constraint 'PK_TENDER'. Cannot insert duplicate key in object 'dbo.TENDER'.

The statement has been terminated.

Row No = 2

1

5

10

0

5

Looks like my cusor is always pointing to the last record of my selection

Please help and Thanks in advance

James

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

I think your approach is not good. Even if you manage to resolve it now, you'll have issues later if going this way.

My suggestion is to re-create your table a bit differently:

CREATE TABLE [dbo].[MYTABLE]

(

[ID] int identity(1,1) primary key,

[TND_CD] [smallint] NOT NULL,

[ACCPT_FOR_SLS_FG] /*???*/

[STR_ID] [int] NOT NULL,

[HIER_ID] [int] NOT NULL,

[LOC_ID] [int] NOT NULL

)

Do not create CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED

you already have primary clustered key (surrogate). You can create non-clustered key instead of the old clustered key.

Then keep on with your exhibitions.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

jds-685721 SSC Enthusiast Points: 128 More actions · Answer 9

Thanks for reply

Unfortunately this is our existing table structure – I can’t change it.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

You do not need to use a loop for this. Just modify the original suggestion so that you only return rows that don't already exist.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jds-685721 SSC Enthusiast Points: 128 More actions · Answer 11

Thanks Sean, It worked!.

If you know how to do it then it becomes very simple 🙂

Thanks all for the helps

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

You are quite welcome. Glad that worked for you.

If possible, can you post what worked you so that others who may stumble across this thread can see a working solution?