Solved

How to looping through table and concatenate in t-sql or sql server?

Posted on 2014-09-15
8
130 Views
Last Modified: 2015-02-11
Hello,

I need to loop through a table (Table1) and as it goes through each single record to concatenate 3 columns (date, time and code) it has to  create a unique column(Primary Key) .

Two questions:

1) Is this a proper of looping on row of a table?
2) why am I getting error as following?

Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "Table1.Date" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "Table1.Time" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "Table1.code" could not be bound.

USE MyDB

while (select count(*) from Table1) > 0
begin

	declare @dt as varchar(10)
	declare @ti as varchar(11)
        declare @code as varchar(11)

	set @dt = Table1.Date
	set @ti = Table1.Time
	set @code = Table.code
	set @dt = REPLACE(@ti, '/','')
	set @ti = REPLACE(@ti, ':','')
	set @code = REPLACE(code, '.','')

   select comb = @code + @dt + @dt
end

Open in new window


Thanks.
Ak
0
Comment
Question by:akohan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Expert Comment

by:skillilea
ID: 40324367
Not sure exactly but...a CTE would run a lot faster.

Anyway here is a looping example.


DECLARE @t TABLE(
      RowID bigint identity (1,1)
      , TheDate datetime
      , TheCode varchar(50)
      )

DECLARE @x bigint

INSERT INTO @t      
      SELECT GETDATE(), NEWID()
      UNION
      SELECT GETDATE() +1 , NEWID()
      UNION
      SELECT GETDATE() + 2, NEWID()




SET @x = (SELECT MIN(RowID) FROM @t)
WHILE @x IS NOT NULL BEGIN

      SELECT
            CONVERT(varchar(50), TheDate, 110) + '_' + TheCode
      FROM @t
      WHERE RowID = @x


      SET @x = (SELECT MIN(RowID) FROM @t WHERE RowID > @x)
END
0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 400 total points
ID: 40324375
There's no need to do a loop for this, it will only slow you down. Run this to see if it gives you what you need. Note that you can also just add a new IDENTITY or UNIQUEIDENTIFIER column if you just need uniqueness. Your method DOES NOT guarantee uniqueness:
DECLARE @Table1 TABLE(
	[ID] VARCHAR(50), 
	[Date] VARCHAR(10), 
	[Time] VARCHAR(11), 
	[Code] VARCHAR(11)
	)

INSERT INTO @Table1 (Date, Time, Code)
	VALUES ('6/1/2014', '16:43:04.79', 'some1.thing'),
		('6/2/2014', '16:44:04.80', 'some2.thing'),
		('6/3/2014', '16:45:04.81', 'some3.thing'),
		('6/4/2014', '16:46:04.82', 'some4.thing'),
		('6/5/2014', '16:47:04.83', 'some5.thing')
	
UPDATE @Table1
	SET ID = REPLACE(REPLACE(REPLACE(Code + [Date] + [Time], ':', ''), '.', ''), '/', '')

SELECT *
FROM @Table1

Open in new window

0
 

Author Comment

by:akohan
ID: 40324385
Hi skillilea,

What is CTE?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:akohan
ID: 40324386
Hi Russel,

Yes, I need uniqueness so should I change the column type to IDENTITY? then run your example?
0
 

Assisted Solution

by:skillilea
skillilea earned 100 total points
ID: 40324389
If you are looking for a unique column set the column to a UNIQUEIDENTIFIER.  Or increment a BIGINT and make it an identity.

SQL does this for you so you don't need to jump through the hoops you are trying to create.

CTE = Common Table Expression.   Can use this technique to replace most loops.

Russel did it best above it you really need to add a field to your table.

sk
0
 

Author Comment

by:akohan
ID: 40324394
Ok, I understand what you are saying now but let me explain my situation.

Here:

I'm getting a a huge list from IIS log file which has thousands of rows on daily basis.  I'm reading the IIS log file and then import them in a table (temporary) which you see here. Now, from here I need to break the information in several tables. One of them is Visitors table. Each visitor is recognized by unique id which I thought ip+date+time would be fine.

Now, are you saying instead of creating combination of date, time, ip i create a unique column in Visitor table? if make sense but can I keep this key in other tables as foreign key?
if so, how can I keep this id to create a relational case between this table (visitor) and table (UrlVisited) table?

Thanks,
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40324482
Well, you still want to avoid deliberate looping through code of your own - if you can - and you can here.

Let the product do the looping for you (think of a sql command as looping row by row automatically)

e.g. this would update those 3 fields in table1

UPDATE Table1 /* "loop" through this table, updating each row */
SET
     Table1.Date = REPLACE(Table1.Date, '/','')
   , Table1.Time = REPLACE(Table1.Time, ':','')
   , Table1.code = REPLACE(Table1.code, '.','')
;

On the broader question of design I don't think I have enough understanding to offer a definite opinion. If your current design of ip+date+time does provide uniqueness then you may not want to change too much. HOWEVER I would suggest you take the date and time as one field and actually store it as a datetime or datetime2 (you might already be doing this).

---------

by the way, it's probably lucky you had syntax issues in your loop, because if you had at least one record in table1 then

while (select count(*) from Table1) > 0

would always be true and there was no way out of that loop.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40325806
You don't need to concatenate everything into a single column to make it a primary key value.

Just define the combination of the three columns as a PK:

ALTER TABLE dbo.Table1
ADD CONSTRAINT Table1__PK PRIMARY KEY ( Date, Time, code )

You're just wasting processing and disk space by concatenating existing columns and storing them again.
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question