• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Using cursor to populate table

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
0
dnsvh
Asked:
dnsvh
  • 3
  • 2
1 Solution
 
CIC AdminCommented:
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-
0
 
dnsvhAuthor 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.
0
 
dnsvhAuthor Commented:
You are correct, by the way, that I can eliminate the @dates table variable once I figure out how to combine the two.
0
 
CIC AdminCommented:
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

0
 
dnsvhAuthor Commented:
That worked.  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now