Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help on SQL script Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 3:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:59 PM
Points: 10, Visits: 36
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, 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 …
Tons of data…

Now I want to read through TBL1 and insert some rows where Col1 =1 with data below
3 2 Val_A Val_B Val_C
3 2 Val_D Val_B Val_C
3 2 ….
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
Post #1537537
Posted Monday, February 3, 2014 3:58 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:56 AM
Points: 592, Visits: 925
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
Post #1537539
Posted Monday, February 3, 2014 4:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 3,010, Visits: 3,086
Are you sure that rows
Col1, Col2, Col3 Col4, Col5
3 2 Val_A Val_B Val_C
3 2 Val_D Val_B Val_C
are not already existing rows?





Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1537542
Posted Monday, February 3, 2014 4:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 17,805, Visits: 15,716
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
Post #1537555
Posted Monday, February 3, 2014 5:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:59 PM
Points: 10, Visits: 36
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
Post #1537567
Posted Monday, February 3, 2014 5:13 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:56 AM
Points: 592, Visits: 925
You get one freebie without create table statements and sample data . 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
Post #1537568
Posted Monday, February 3, 2014 5:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:06 PM
Points: 17,805, Visits: 15,716
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
Post #1537571
Posted Tuesday, February 4, 2014 1:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:59 PM
Points: 10, Visits: 36
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]
1 10 0 0 1
1 10 0 0 2
1 10 0 0 3
1 10 0 0 4
1 10 0 0 5
2 11 0 0 1
2 11 0 0 2
2 11 0 0 3
2 11 0 0 4
2 11 0 0 5
8 10 1 0 1
8 10 2 0 2
8 10 3 0 3
8 10 4 0 4
8 10 5 0 5

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
Post #1537931
Posted Tuesday, February 4, 2014 1:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 3,010, Visits: 3,086
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,
SQL Server developer at Seavus
www.seavus.com
Post #1537937
Posted Tuesday, February 4, 2014 6:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:59 PM
Points: 10, Visits: 36
Thanks for reply
Unfortunately this is our existing table structure – I can’t change it.
Post #1538006
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse