June 13, 2010 at 8:14 pm
I've data as follow,
RID |Posi |BusN | DDate
-----------------------------------------------------------------------
_R1004000000011WKM19252010-06-16 00:00:00
_R1004000000011WKM19252010-06-17 00:00:00
_R1004000000011WKM19252010-06-18 00:00:00
_R1004000000011WKM19252010-06-19 00:00:00
_R1004000000011WKM19252010-06-20 00:00:00
_R1004000000011WKM19252010-06-21 00:00:00
_R1004000000011WKM19252010-06-23 00:00:00
_R1004000000011WKM19252010-06-24 00:00:00
_R1004000000011WKM19252010-06-25 00:00:00
_R1004000000011WKM19252010-06-26 00:00:00
_R1004000000011WKM19252010-06-27 00:00:00
_R1004000000011WKM19252010-06-28 00:00:00
_R1004000000011WKM19252010-06-30 00:00:00
_R1004000000011WKM19252010-07-01 00:00:00
_R1004000000011WKM19252010-07-02 00:00:00
_R1004000000011WKM19252010-07-03 00:00:00
_R1004000000011WKM19252010-07-04 00:00:00
_R1004000000011WKM19252010-07-05 00:00:00
_R1006000000011WKM19252010-06-15 00:00:00
_R1006000000011WKM19252010-06-16 00:00:00
_R1006000000011WKM19252010-06-17 00:00:00
_R1006000000011WKM19252010-06-18 00:00:00
_R1006000000011WKM19252010-06-19 00:00:00
_R1006000000011WKM19252010-06-20 00:00:00
_R1006000000011WKM19252010-06-21 00:00:00
_R1006000000011WKM19252010-06-22 00:00:00
_R1006000000011WKM19252010-06-23 00:00:00
_R1006000000011WKM19252010-06-24 00:00:00
_R1006000000011WKM19252010-06-25 00:00:00
_R1006000000011WKM19252010-06-26 00:00:00
_R1006000000011WKM19252010-06-27 00:00:00
_R1006000000011WKM19252010-06-28 00:00:00
_R1006000000011WKM19252010-06-29 00:00:00
_R1006000000011WKM19252010-06-30 00:00:00
_R1006000000011WKM19252010-07-01 00:00:00
_R1006000000011WKM19252010-07-02 00:00:00
_R1006000000011WKM19252010-07-03 00:00:00
_R1006000000011WKM19252010-07-04 00:00:00
_R1006000000011WKM19252010-07-05 00:00:00
_R1006000000011WKM19252010-07-06 00:00:00
I've tables as follow,
create table t6
(idx int identity(1,1), RID varchar(30), Posi tinyint, BusN varchar(10), DDate datetime);
create table t7
(idx int identity(1,1), RID varchar(30), Posi tinyint, BusN varchar(10), DDate datetime);
My question is
1. How to insert above resultset into t6 and t7.
2. if DDate=2010-06-16, this row insert into t6, and so on
3. if DDate=2010-07-01, this row insert into t7 , and so on
June 14, 2010 at 5:19 am
If you have 12 tables to insert into, you will need to run 12 separate insert queries.
If you want to write minimum of code, you can use loop and dynamic sql. Something like this:
DECLARE @sql NVARCHAR(5000)
DECLARE @m INT
SET @m = 1
WHILE @m <= 12
BEGIN
SET @sql = 'Insert into t' + cast(@m as varchar) +
' select ... from ... ' +
' where MONTH(datecolumn) = ' + cast(@m as varchar)
EXEC sp_executesql @sql
END
June 17, 2010 at 1:39 am
Hi sir,
With your guidance, now i know how to insert into t6 and t7.
But if my resultset as follow,
RID |Posi |BusN | DDate
-----------------------------------------------------------------------
_R100400000001 1 WKM1925 2010-06-16 00:00:00
_R100400000001 1 WKM1925 2011-06-17 00:00:00
_R100400000001 1 WKM1925 2010-07-18 00:00:00
_R100400000001 1 WKM1925 2010-06-19 00:00:00
_R100400000001 1 WKM1925 2011-07-20 00:00:00
_R100400000001 1 WKM1925 2012-07-20 00:00:00
....
....
....
How to insert into t62010, t62011, t72010, and t72011, t72012and so on?
I'm stuck to adjust the query
June 17, 2010 at 3:22 am
--First, lets build some test data
DECLARE @table AS TABLE(
rid VARCHAR(50),
posi INT,
busn VARCHAR(7),
ddate DATETIME)
--Using the test data you specified in your post
INSERT INTO @table(rid,posi,busn,ddate)
SELECT '_R100400000001',1,'WKM1925','2010-06-16 00:00:00'
UNION ALL SELECT '_R100400000001',1,'WKM1925','2011-06-17 00:00:00'
UNION ALL SELECT '_R100400000001',1,'WKM1925','2010-07-18 00:00:00'
UNION ALL SELECT '_R100400000001',1,'WKM1925','2010-06-19 00:00:00'
UNION ALL SELECT '_R100400000001',1,'WKM1925','2011-07-20 00:00:00'
UNION ALL SELECT '_R100400000001',1,'WKM1925','2012-07-20 00:00:00'
--Now, declare the variables
DECLARE @sql NVARCHAR(4000)
DECLARE @end INT
--@end is set to the total number of rows in the test data,
--this means we get 1 insert statement per row
SELECT @end = COUNT(1)
FROM @table
--@m is the start, used as a counter, slightly modifed from
--the previous answer to your question.
DECLARE @m INT
SET @m = 1
--Now, because there is no unique way to keep track of
--the different data we are creating inserts for, I've
--created a temp table including an identity field called
--unid.
DECLARE @temp AS TABLE(
unid INT IDENTITY,
rid VARCHAR(50),
posi INT,
busn VARCHAR(7),
ddate DATETIME)
--Fill it with the test data, the identity column is
--iterated automatically.
INSERT INTO @temp(rid,posi,busn,ddate)
SELECT *
FROM @table
--Now we get to the solution! While our counter is less than
--or equal to the total number of records we create a SQL
--statement.
WHILE @m <= @end
BEGIN
--Rather than using the previous version, I have grabbed the month
--as a varchar and the year as a varchar and added them to the
--variable @sql.
SELECT @sql = 'INSERT INTO t' + CAST(MONTH(ddate) AS VARCHAR) + CAST(
YEAR(ddate) AS VARCHAR) +
' SELECT ... FROM ... ' +
' WHERE MONTH(datecolumn) = ' +
CAST(MONTH(ddate) AS VARCHAR)
FROM @temp
--This is where our temp table comes into play. This allows us
--to grab each row and creating an individual statement for each.
WHERE unid = @m
EXEC Sp_executesql @sql
--Iterate the counter.
END
I think this could be done in a better way. I've had to use a temp table due to the lack of any unique identifier in the data you supplied.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply