Query Help :copying rows

[artificiality]

Hi ,

I need to copy rows from a table to a specific location in the same table.

I suppose that it is possible to copy rows from a table to the same table
as followed :

INSERT INTO Table
SELECT * FROM Table
WHERE Condition

How can I insert into a specific location based on another condition. ?

Thanks for the help .

 

[pbaldy]

The concept of rows in a specific order in the table is not valid (that's spreadsheet thinking). You put them in the order you want when you view them, through queries, forms and reports.

 

[artificiality]

OK.
How do I copy rows ( part of the fields) of a table into the same table and filling the other fields with specific data with a single query?

 

[pbaldy]

You could do what you described, but without the wildcard:

INSERT INTO TableName(Field1, Field2, Field3)
SELECT Field1, Field2, "SpecificData"
FROM TableName
WHERE Whatever

 

[artificiality]

OK.
What about the ID field ?The ID field is field1(autonumbered) and there is no way I can know in advance their values for the inserted rows.

Should I do :


INSERT INTO TableName( Field2, Field3)
SELECT Field2, "SpecificData"
FROM TableName
WHERE Whatever

?

 

[pbaldy]

Yes, you never have to include the autonumber field in the insert.

 

[artificiality]

How do we select a specific Date format field ?

i.e Date field is 1/10/2007

Select field1,field2,1/10/2007
Where Condition ?

 

[pbaldy]

Select field1,field2, #1/10/2007#

 

[artificiality]

Thanks.
I figured it out.

What is the problem with the following query :

"INSERT INTO RawData(RunID,fullName,name,category,type,subType,numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines,virtual,#1/3/2007#,namespace WHERE namespace = 'customer.demo' AND RunID = 256" ?

 

[pbaldy]

No FROM clause for one thing.

 

[artificiality]

How could I forget it ?

 

[artificiality]

sqlSt = ""
sqlSt = sqlSt & "INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace)"
sqlSt = sqlSt & " SELECT " & RunID & " ,fullName,name,category,type,subType,numberOfLines ,virtual," & "#" & d & "#" & ",namespace FROM RawData "
sqlSt = sqlSt & " WHERE namespace = " & "'" & namespace & "'" & " AND RunID = " & id

id = 256

Why do I get on debug for sqlSt :

"INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines ,virtual,#1/3/2007#,namespace FROM RawData WHERE namespace = 'customer.demo' AND RunID "

Where is the id value ?

 

[pbaldy]

Don't know; same place the "=" went, I guess. I assume that

id = 256

is really before that point?

 

[artificiality]

Yes, before.
On debug I have id = 256 which is expected.

 

[pbaldy]

Not sure, since it properly picked up the other values. Can you post a sample db?

Bad form to post the same question twice, btw.

 

[artificiality]

Sorry for that.

Why does the "=" disappear as well ?

 

[pbaldy]

Not sure; that's what makes it curious. You're near 255 characters, which is the limit for some things, but not string variables. How is that variable declared?

 

[artificiality]

Dim sqlSt As String

 

[pbaldy]

Then I'm stumped. I cut and pasted your string into a VBA string, and added a bunch of characters, and it held them fine, so it's not a length problem. No sample available?

 

[artificiality]

I removed 2 blank spaces from the string :

sqlSt = ""
sqlSt = sqlSt & "INSERT INTO RawData(RunID,fullName,name,category,type,subType,numberOfLines,virtual,date,namespace)"
sqlSt = sqlSt & " SELECT " & RunID & " ,fullName,name,category,type,subType,numberOfLines,virtual," & "#" & d & "#" & ",namespace FROM RawData"
' sqlSt = sqlSt & " FROM RawData "
sqlSt = sqlSt & " WHERE namespace =" & "'" & namespace & "'" & " AND RunID =" & id


And the result is fine.

I have no idea why the removal of 2 spaces is necessary.