We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Using cursor to populate table

dnsvh
dnsvh asked
on
Medium Priority
337 Views
Last Modified: 2008-03-17
Hello,

I need to create a table variable to hold dates associated with serial numbers.  Here is what I have so far:

--Create table variable to hold list of serial numbers for Clark County or Parkrose users
DECLARE @serials TABLE
(
  RowID INT IDENTITY PRIMARY KEY NONCLUSTERED,
  Serial VARCHAR(5) NULL
)

--Insert values into variable
INSERT @serials
SELECT
Serial
FROM SERVER.Main.dbo.UserInfo
WHERE LEFT(Location, 3) IN('PAR', 'CLA')

UNION

SELECT
Serial
FROM SERVER.Archive.dbo.UserInfo
WHERE LEFT(Location, 3) IN('PAR', 'CLA')

--Create table variable to hold dates associated with serial numbers
DECLARE @dates TABLE
(
  RowID INT IDENTITY PRIMARY KEY NONCLUSTERED,
  EventDate DATETIME NOT NULL
)


DECLARE @count INT
SELECT @count = 1
WHILE @count < 5
BEGIN
      INSERT @dates
      SELECT CONVERT(DATETIME, '2006-' + CONVERT(CHAR(1), @count) + '-1')
      SELECT @count = @count + 1
END

SELECT *
FROM @dates


So far, the @serials variable holds a list of unique serial numbers and the @dates variable holds 4 dates like this:

RowID     EventDate
1            2006-01-01 00:00:00.000
2            2006-02-01 00:00:00.000
3            2006-03-01 00:00:00.000
4            2006-04-01 00:00:00.000


What I need to do is to join the two together to come up with something like this:

RowID     SerialNo     EventDate
1             2356         2006-01-01 00:00:00.000
2             2356         2006-02-01 00:00:00.000
3             2356         2006-03-01 00:00:00.000
4             2356         2006-04-01 00:00:00.000
5             2375         2006-01-01 00:00:00.000
6             2375         2006-02-01 00:00:00.000
7             2375         2006-03-01 00:00:00.000
8             2375         2006-04-01 00:00:00.000
etc ...

I think that I may need to use a cursor to accomplish this but I'm not sure.  Can anyone help me to figure this out?

Derrick
Comment
Watch Question

I am not quite sure what purpose the @dates table is performing.  Since you create the date dynamically, it doesn't appear to be a real date.

You should be able to combine the two functions into one with something like :

DECLARE @serials TABLE
(
  RowID INT IDENTITY PRIMARY KEY NONCLUSTERED,
  Serial VARCHAR(5) NULL
  EventDate DATETIME NOT NULL
)

Then remove the @Dates table declaration and use the @Serials in your loop instead.

DECLARE @count INT
SELECT @count = 1
WHILE @count < 5
BEGIN
     INSERT @serial
     SELECT CONVERT(DATETIME, '2006-' + CONVERT(CHAR(1), @count) + '-1')
     SELECT @count = @count + 1
END

Unless I am missing something, the @serial table would then match what you want your final result to be.

Good luck,
-kent-

Author

Commented:
You are missing the serial numbers.  The insert statement you have only inserts the dates.  I need to match the dates to the list of serial numbers.

Author

Commented:
You are correct, by the way, that I can eliminate the @dates table variable once I figure out how to combine the two.
Yep, I jumped the gun on that a bit.  We'll go back to adding the @dates table, but add in a Serial field.  

Try the following modifications :

--Create table variable to hold dates associated with serial numbers
DECLARE @dates TABLE
(
  RowID INT IDENTITY PRIMARY KEY NONCLUSTERED,
  Serial VARCHAR(5) NULL
  EventDate DATETIME NOT NULL
)

DECLARE @count INT
SELECT @count = 1
WHILE @count < 5
BEGIN
     INSERT @dates (Serial, EventDate) VALUES SELECT Serial, CONVERT(DATETIME, '2006-' + CONVERT(CHAR(1), @count) + '-1') FROM @Serial
     SELECT @count = @count + 1
END

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That worked.  Thank you!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.